Sum Check Between an Ex_ID & Amount

Question

Hello,

I am currently trying to put in a check that will essentially allow me to recognize if amounts are somehow missing from my data/workflow.

As of right now, my External ID field has the sum of the total amounts that go along with that payment date, within the actual ID. These sums are identifiable as they come right after the dash in each External ID.

What I am trying to figure out is how to Sum each amount that corresponds to the correct external ID & payment date and make sure that all those amounts sum up and match the amount that is within the External ID

For Instance, the sum of Amounts 6622+4800+3600+3000 should equal 18022, as seen in the External ID.

The catch is also I need Alteryx to recognize when to start a new summation, when the External ID changes. Any suggestions?

Thanks!

See attached image for context.

alteryxquestion2.png

ethankutch · Answer

Hey caltang,

Thanks so much for your help so far. A further showcase would be great. I think I understand how you can pull the amount from External ID, but am still confused about how to implement it so would restart the summing when the External ID changes (in essence checking each External ID so that sum of amounts checks out).

Here's some sample data and a copy of the workflow I have so far.

New Workflow2.yxmd

exampledata.xlsx

caltang · Answer

If the above is too difficult to follow, perhaps you can share some sample data with us here, then we showcase further.

Hope this helps.

caltang · Answer

I think the first order of business is to get the amount out of the string, so then you can compare. I've done a part of it for you, use the same idea to get the amount out, then use the Summarize tool by grouping the string and sum the values, then use the Join tool to join back to the main data and compare against the total amount. Use a Filter tool to split out those that don't match, then you have your way of checking complete.

If you have null values in your values, then you need to use a Data Cleansing tool to replace them with 0 so that you can apply mathematical functions on the values, otherwise it will not work as intended.

for ethan_part1.yxmd