5 Replies Latest reply on May 22, 2017 12:43 PM by Okechukwu Ossai

# Related Row Look Up

Hello,

New to Tableau and have a formula question.

I have a excel sheet that has a list of transaction details that I am using as my source. Each transaction has a unique SVC ID (shown in column one below) but has multiple rows per transaction with some data only found in a specific row.

Below is a example of how one transaction can look:

 Svc ID Svc Units Svc Fees Svc Payments Svc Refunds Svc Misc Debits Svc Adjustments Pmt Transaction Desc Pmt Amount 1111 1 250 14.34 0 0 150.78 0 1111 0 0 14.34 0 0 150.78 Payment 14.34 1111 0 0 14.34 0 0 150.78 Adjustment 150.19 1111 0 0 14.34 0 0 150.78 Adjustment 0.59

In Tableau, I am only looking at the second row, i.e. rows that have "Payment" in the "PMT Transaction Desc" column but I also need "Svc Fees" which is found in the first row.

I cant use the LOOKUP function with a -1 offset as the "payment" row is not always #2 in order.

So is there a way to have a calculated field that would return the service fee when greater then 0 for transactions with the same Svc ID?

or

A formula that would copy the service fee from a row with blank "PMT Transcation Desc" to all other related SVC ID rows?

I hope this question makes sense.

Thanks!

Edwin

• ###### 1. Re: Related Row Look Up

Edwin,

You can do this in 2 ways; using LOD or table calculation.

1. Using LOD. Create calculated field [Max Svc Fees]

{ FIXED [Svc ID]: MAX([Svc Fees])}

2. Alternatively, using table calculation. Create calculated field [Max Svc Fees v2]

WINDOW_MAX(IF ATTR([Svc ID]) = MIN([Svc ID]) THEN MAX([Svc Fees]) END)

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 2. Re: Related Row Look Up

Hi Ossai,

Is there a way to calculate Max Sev Fees without using an aggregate calculation at the Viz level?

I would like to have other calculated fields based Max Svc Fees at the data source level. Do you think this is possible?

Edwin

• ###### 3. Re: Related Row Look Up

Hi Edwin,

Yes, option 1 using LOD will get you the result you are looking for.

[Svc ID] needs to be a dimension for it to work. This the best option. Remember you are asking Tableau to assign a value to a row with data from a different row. This is not very straightforward and using FIXED LOD will get the job done for you.

Ossai

• ###### 4. Re: Related Row Look Up

Thanks Ossai!

This worked!

• ###### 5. Re: Related Row Look Up

Always welcome! I'm glad it helped.