2 Replies Latest reply on Aug 16, 2018 5:53 AM by Toni M

# Search a value from one column/dimension in another column/dimension - VLOOKUP equivalent

Hi all,

I've been looking on this forum for a solution to my problem but couldn't find anything. In simple terms what I want is to look if a value from one dimension is present in another dimension(down the window), similar to VLOOKUP in excel. One problem I have is that my data source is Legacy BigQuery which doesn't support LODs.

Detailed:

I have some transactions ID which are unique but will have different forms for the same customer. 123456 for the innitial transaction and 123456_abc for any subsequent additions to the innitiall order. A complication with our set-up is that a customer can purchase something on a different website (they would have the 123456 transaction but we would have no visibility on it) and then purchase something on my site so I would only see 123456_abc.  So, I need to check if on top of the second transaction they also had a first transaction on my site.  Madness, i know

So for a list of  4 transactions: 1234 , 1235, 1235_abc, 1236_abc my approach was to first create a calculated field that would get rid of "_abc" so I ended up with something like this:

TransIDTransID (calculated)
1234null
1235null
1235_abc1235
1236_abc1236

The expected result would then be

TransIDTransID (calculated)Original trans available?
1234nullYes
1235nullYes
1235_abc1235Yes (as it's the same main ID as above)
1236_abc1236NO (as 1236 can't be found anywhere in the TransID list)

Thanks for the help

• ###### 1. Re: Search a value from one column/dimension in another column/dimension - VLOOKUP equivalent

Hi, Toni

Not sure whether you could do a join your data to itself or not, but just give a solution may suit your case.

below is the steps

1st, join your data to itself by key 1=1

2nd, create a calculation field as flag and then you can make it as yes/no, I just show the true/false

Hope this helps

ZZ

• ###### 2. Re: Search a value from one column/dimension in another column/dimension - VLOOKUP equivalent

Hi, thanks for the try but as I said I can't use LODs due to limitations with my data source. Thanks anyways