2 Replies Latest reply on May 18, 2016 1:22 AM by Selim Dayanik

    VLOOKUP type function required to assign related data values to different data sets

    Selim Dayanik

      Hi everyone,

       

      I'm currently struggling to perform a VLOOKUP style task in Tableau.

       

      I have three separate data sources in my example and all of them are Excel spreadsheets, in real life the SAP data is a live connection from SAP (otherwise I wouldn't have an issue as I could do the VLOOKUP in Excel).

       

      1. Forecast and Actual Data - two tabs
        1. Forecast - tab
        2. Actual - tab
      2. SAP Test Data
      3. Category Test Data

       

      My end goal is to create a clustered bar chart that displays the total number of days worked each month with three different series of information - Forecast, Actual and SAP (example screenshot attached). I want the ability to add a filter on my dashboard to filter by Category_Name.

       

      My biggest problem is that the SAP data does not contain the correct Category_Names, instead it contains a Category_Number, so I can't create a relationship between SAP and the corresponding Category_Name fields in Forecast and Actual.

       

      I do have a data table that relates the Category_Name to the Category_Number - This is the Category Test Data spreadsheet shown above in point 3.

       

      If I had this problem in Excel I would simply do a VLOOKUP from data source 3 and add the results in a new column into data source 2.

       

      See below worked example.

       

      Category_Number = T54 - ABC12 **I would need to split this in Tableau so I only have the left three characters T54

      Cat_ID = T54

      Category_Name = Category 1

       

      If I could perform this data blending or data joining or whatever it is in Tableau, then I can easily add my dashboard filter as everything will correlate. Unfortunately I'm completed stumped on how to do this so any help would be greatly appreciated.

       

       

      N:B

       

      My dates are also different across my data sets, but this isn't an issue as I can create calculated fields to show me the month value for dates formatted as "dd/mm/yyyy" using the following formula DATENAME('month',[Date])