Using Tableau to do VLookup

Question

New to Tableau so sorry if this is obvious, but I had a hunt and couldn't find/understand anything. So I have a cake shop and I want to know if people have purchased both éclairs and Eccles cakes in the same order

ORDER ID     item 
0001         éclair
0001         iced bun
0001         Eccles cake
0002         éclair 
0002         iced bun
0003         éclair          
0003         Eccles cake     

What I want as an output is the following:

ORDER ID     item          éclair?
0001         éclair          0
0001         iced bun        0
0001         Eccles cake     1
0002         éclair          0
0002         iced bun        0
0003         éclair          0
0003         Eccles cake     1

I'm thinking it requires something like a lookup, or a helper column?!

For reference, I've got a single data set (a csv file) so I'm guessing I can't run a SQL query on it?

Answer 1

Here is how I was able to do this - there may be a better way that I am not familiar with. Nonetheless...

---

Steps:

1. In a sheet, add Order Id and Item to the Rows section.

2. Duplicate your data source (right click > Duplicate). I'll refer to the original as A and the copy as B.

3. Unlink the Item field. This is done by clicking the orange chain link icon to the right of the field name. A gray link is what you want. Leave the Order Id field linked (orange).

4. Staying on the same sheet, click on data source B and create these two calculated fields: Cake_Flag: MAX(IF [Item] = 'Eccles cake' THEN 1 ELSE 0 END)

   Eclair_Flag: MAX(IF [Item] = 'éclair' THEN 1 ELSE 0 END)

5. Return to data source A by selecting it and create this new calculated field: éclair?: IF ATTR([Item]) = 'Eccles cake' AND [Data Source B].[Eclair Flag] = 1 AND [Data Source B].[Cake Flag] = 1 THEN 1 ELSE 0 END

   [Data Source B] should be replaced with whatever the name of your B data source is.

6. Place the new calculated field éclair? in the Text card.

---

Result: