5 Replies Latest reply on Jul 20, 2016 5:20 PM by Tien Nguyen

# Lookup Function - returning another dimension

Hello there,

Due to data confidentiality, I have created a simple data set below that illustrates my task at hand. Based upon three dimensions, I would like to create a new calculated dimension.

Current table:

Invoice NumberCommodityInvoice Count (aka % of Line Items with designated commodity for single invoice)
1000Office Supplies0.8
1000IT Hardware0.2
1001Office Supplies0.1
1001IT Hardware0.2
1001Office Furniture0.7

The heart of an issue is that one invoice can comprise of multiple commodities. I need to simplify to a relationship of 1 invoice to 1 commodity. I would like to create a calculated dimension- Adjusted Commodity - that follows the following logic. For every invoice number, lookup the max(Invoice Count) and return the Commodity.

Desired output:

1000Office Supplies
1001Office Furniture

I have not been able to figure out how to use the lookup function to return another dimension. I have tried using an IF statement but with no success.

Any suggestions?

• ###### 1. Re: Lookup Function - returning another dimension

Hi Jackie,

Please find the attached screenshot and confirm the output. Create a calc field as below. Put it to the rows section as well as filter section(exclude null from the same. Refer the screenshot).

if MAX({ FIXED [Invoice Number] : MAX([Invoice cnt]) })=SUM([Invoice cnt])

then ATTR([Commodity])

END

Let me know if this helps. Attached workbook is created in tableau 9.3 version.

Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: Lookup Function - returning another dimension

You can try this

Step 1: Find MAX for each invoice number regardless of its commodity

{EXCLUDE [Commodity]: MAX([Invoice Count])}

Step 2: Simple IF Statement

IF [Invoice Count] = [Max Invoice Count] THEN [Commodity]

END

• ###### 3. Re: Lookup Function - returning another dimension

Hello Ashish,

Thank you for the quick response! It is definitely close. My Tableau version 9.0 will not allow me to use Agg(Adjusted Commodity) as a filter. I will try to see if there is a difference if I upgrade to 9.3...

Thank you!

Jackie Kinney

• ###### 4. Re: Lookup Function - returning another dimension

Hello Ashish and Tien Nguyen

Good News: I was able to copy your solution for Tableau 9.3 and it worked!

Bad News: My company will not let me use Tableau 9.3 for publishing - I must use 9.0. In 9.0, I cannot filter on AGG(Adjusted Commodity); filtering is not available for that calculated field.

Tien - I tried to use your calculations but I was not able to have it work in version 9.0 either.

Do either of you know of a solution that can be used with 9.0? I've attached a copy of the workbook sample.

Thanks!

• ###### 5. Re: Lookup Function - returning another dimension

Hi Jackie,

That is surprising since I use LOD which is available from Tableau 9.0.

Anyhow, I guess with can use this instead. It uses index() function together with table calculation so it must work with all Tableau version

The idea is  finding "top 1 Commodity For each Invoice"

Attached is the worknbook again