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).
- Forecast and Actual Data - two tabs
- Forecast - tab
- Actual - tab
- SAP Test Data
- 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.
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])