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

[ShankerV]

Hi @Travis_Ratliff 

There is no input file in the alteryx workflow.

Its the local path of your machine, please share the input.

Shanker V

[Travis_Ratliff]

Sorry about that. Here is the data.

[braveraj]

Can you just use a multi-row formula tool and group by contact service agreement?

[ShankerV]

@Travis_Ratliff 

Sample file for Transaction please, expected is 2 input files right?

Shanker V

[Travis_Ratliff]

The file has two different sheets in it. One for each

[Travis_Ratliff]

How would I join the two of them together before that step? 

Mine has two different files which is part of the problem

Thanks

[ShankerV]

Hi @Travis_Ratliff 

Got it, working on the solution. Few minutes, will get back to you.

Shanker V

[braveraj]

So I'm not sure on the joins but I've tried trimming the date on transaction as a join key. Ultimately it created a product join on just service agreement code, but if you're only looking for where the price and rate are the same by agreement you can filter it down.

[ShankerV]

Hi @Travis_Ratliff 

As the two inputs need to be combined with contract service

We have 140 records in Contract Product and 17858 records in Transaction, 

Using the join tool, it is trying to figure out each combination with contract service and generating 179,336 records in output. Which is ultimately not correct.

Need one more matching criteria, also as you can explain how the data can be concatenated.

Many thanks

Shanker V