2 Replies Latest reply on Aug 5, 2012 6:30 PM by Nathan Schofield

    VLOOKUP within one data source

    Nathan Schofield



      I am trying to create a view in Tableau where I compare some data from within the same source table. I have attached an Excel file demonstrating the problem and hope that someone can help me prototype it in Tableau.


      In the attached Excel file I show the raw data on the tab 'Raw Data' and my Excel solution on the tab 'My Excel Solution'.



      Rank, Category, Market, Type, AppID, Estimate


      There are several ranks, two categories, x types, many App IDs and estimates. I want to compare the estimate from combination Market-Type-AppID between the two categories to see the % difference. I have done this using a key and VLOOKUP in Excel, but have not been able to replicate or find a solution in Tableau.


      Another fact is that I want to be able to view the results in a similar fashion to what they are displayed originally, i.e. to view Games ranks 1-15(ordered) with columns market, type, App ID and estimates from two categories where available.


      If someone can prototype this in Tableau I would be very grateful.





        • 1. Re: VLOOKUP within one data source
          Mark Holtz

          I think you can accomplish this with a join to a copy of the data source.

          (In Excel vernacular, the lookup range is a 2nd table, so you just have to create such a table in Tableau.)


          This is accomplish in Tableau by bringing the data source in again and then going to the Data menu, Edit Relationships, and then setting the "Key Current" calculated field = to the "Key Other" field from the second data source.


          However, I am unsure how your lookup function in Excel is determining which Keys to link, because the key value does not provide enough specificity to identify a single row. The "Other Estimate" column is checking the "Key Other" field value against the "Key Current" column, but depending on how the data is sorted, even with your "FALSE" argument, Excel will complete the lookup and result with the first exact match it finds in the array.


          My guess is that you also want to match on the App ID.

          I have attached what I came up with so far.

          • 2. Re: VLOOKUP within one data source
            Nathan Schofield

            Hi Mark,


            Thank you for that suggestion. It worked, but it seems strange that I have to use this technique to do what I want. I guess it's about the data structure.