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

    Toni M

      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