Forum Discussion
Match two columns then return value based on third column
Hi all,
I have a table of data that looks like this
TransactionID | SubID | Transaction Type | Transaction Date
1011 1012 Billed Order 2022-08-03 14:14:07
1012 1013 Sub Order 2022-08-03 14:14:06
1013 0 Master Order 2022-08-03 14:14:05
Explanation of the data
The Master Order is first created when customer places an order, the sub order is then created to help facilitate the transaction and once results are obtained an billable order is created. Therefore we have 3 sperate rows that relate to one transaction in this example.
What im trying to do
I'm trying to figure out a way to add a column called Original ID that matches these 3 rows and then return the original Master Order ID which is 1013 against all 3 rows.
Example of what it should look like:
TransactionID | SubID | Transaction Type | Transaction Date | Original ID
1011 1012 Billed Order 2022-08-03 14:14:07 1013
1012 1013 Sub Order 2022-08-03 14:14:06 1013
1013 0 Master Order 2022-08-03 14:14:05 1013
I have tried using this just to find a match which works but im not sure how to build on it or if im on the right path.
Hi,
On your dummy dataset, this calculated column formula will work
=CALCULATE(MIN(Data[Transaction Date]),FILTER(Data,Data[Transaction Type]="Master Order"))
However there is something missing in your data. There should be some code (in another column) that should be common for those 3 rows so that we know that those below to one bucket. Once you share that column, will have to insert that additional condition in the FILTER() function using the EARLIER() function.
Hope this helps.
3 Replies
Hi,
On your dummy dataset, this calculated column formula will work
=CALCULATE(MIN(Data[Transaction Date]),FILTER(Data,Data[Transaction Type]="Master Order"))
However there is something missing in your data. There should be some code (in another column) that should be common for those 3 rows so that we know that those below to one bucket. Once you share that column, will have to insert that additional condition in the FILTER() function using the EARLIER() function.
Hope this helps.
Thanks for the help Ashish,
Thats one of the problems with this dataset im working with, there is no common generated key in a seperate column.
The only other possibiliy i can see is the [Order Reference] column where the client would enter in their reference when placing an order. Usually they enter their email address but that field is free text. This Order Reference text would be common across the 3 rows. Could we use that?
Hi,
Yes, we can. There should be some common reference in a spare column.
