Comparing prices of different inputs

[Travis_Ratliff]

Hey all,

I have attached a workflow with my two sample inputs.

The end result that I would want would look like:

Customer_company_name	Contract_service_agreement	transaction_begin_date	transaction_end_date	Price	Rate	Match
Jacks	39975	202207010700	202207010700	30.4	30.4	Match
In-N-Out	33085	202207051000	202207052100	200	[Null]	No Match

But for all of the rows. So, what am I trying to do is join the two inputs and then create a new column that checks if there is a match between the price and rate but only for that contract agreement number. For example, if there was a rate of 200 that was NOT agreement 33085 it still would not match. 

The end result would be the rows as the transactions and the same columns but with that match column added. 

I have unsuccessfully tried multiple ways to do this because it seems relatively easy in my head because I already have this built in excel I just want to automate it.

Any help would be appreciated

[Travis_Ratliff]

I was able to join them by adding a record ID to both and then using a join/union together. So now my issue is checking that the prices have a match in the rates for the agreement

[braveraj]

@Travis_Ratliff did you try using the mulit-row formula tool I provided to match prices with rates?

[Travis_Ratliff]

The problem with using the multi-row formula tool you gave is that it only compares the price with the rate in the same row and not the entire column

[Travis_Ratliff]

If it helps the way that I was able to do this in excel was:

=IF(AND(MATCH($B2,'Contract Products'!B:B,0),MATCH($E2,'Contract Products'!F:F,0)), "Match", "No Match")

[braveraj]

Hi @travis_Raliff

I guess I'm not understanding the original ask. When you join the transaction table to the contract, couldn't you join on agreementID and price == rate? That would be even simpler than a multirow formula tool.

[Travis_Ratliff]

No because when you join on the agreement ID you get 170,000+ rows

[braveraj · Accepted solution]

@Travis_Ratliff If you join on agreement_id and rate == price it drops it from 170K records to 56.