Comparing prices of different inputs

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

All comments

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

Sorry about that. Here is the data.

Sample File.xlsx

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

Sample Data - Row Tool.yxmd

@Travis_Ratliff

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

Shanker V

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

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

Mine has two different files which is part of the problem

Thanks

Hi @Travis_Ratliff

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

Shanker V

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.

Sample Data - Row Tool.yxmd

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

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

Sample Data.yxmd

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

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

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.