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

    Related Row Look Up

    Edwin Niculaescu

      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 IDSvc UnitsSvc FeesSvc PaymentsSvc RefundsSvc Misc DebitsSvc AdjustmentsPmt Transaction DescPmt Amount
      1111125014.3400150.780
      11110014.3400150.78Payment14.34
      11110014.3400150.78Adjustment150.19
      11110014.3400150.78Adjustment0.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