Hi everyone,
Do we have a way to dynamically Sum/Match entries in Alteryx? I have a lot of records that require me to check if they are match or Outs.
The records come from 2 different systems, X and Y. They are identified by ID (2 records can share the same ID), then at a higher level is Security Code which contains some IDs, and then Unit which contains some Security codes. Then we have the Amount for each entry.
Example:
Source | Unit | Security code | ID | Amount |
| X | 12345 | 5AAA | 45323211 | 100 |
| X | 12345 | 5AAA | 45323224 | 200 |
| Y | 12345 | 5AAA | 45323213 | -50 |
| Y | 12345 | 5AAA | 45323213 | -50 |
| Y | 12345 | 5AAA | 45323226 | -200 |
Now, we match based on Source, Unit, Security code, and Amount. The example above is a match. The way I did it is using Summarize tool to transpose the data so group everything up by Source, Unit, Sec code, and Sum by Amount, and then we compare X Source column with the Y source column to see if the Sum of Amounts =0 to create matches:
Source | Unit | Security code | Amount | Break Check: |
| X | 12345 | 5AAA | 300 | Match |
| Y | 12345 | 5AAA | -300 | Match |
Result (we use Join to return the Break Check of Match back to all entries with the same Source, Unit, and Sec Code):
Source | Unit | Security code | ID | Amount | Break Check |
| X | 12345 | 5AAA | 45323211 | 100 | Match |
| X | 12345 | 5AAA | 45323224 | 200 | Match |
| Y | 12345 | 5AAA | 45323213 | -50 | Match |
| Y | 12345 | 5AAA | 45323213 | -50 | Match |
| Y | 12345 | 5AAA | 45323226 | -200 | Match |
However, if I have 1 extra entry that is invalid, the whole summarize tool will be wrong and all records will be Out instead of only the 1 extra record being Out:
Source | Unit | Security code | ID | Amount |
| X | 12345 | 5AAA | 45323211 | 100 |
| X | 12345 | 5AAA | 45323224 | 200 |
| Y | 12345 | 5AAA | 45323213 | -50 |
| Y | 12345 | 5AAA | 45323213 | -50 |
| Y | 12345 | 5AAA | 45323226 | -200 |
| Y | 12345 | 5AAA | 45323226 | -250 |
Summarize:
Source | Unit | Security code | Amount | Break Check: |
| X | 12345 | 5AAA | 300 | Out |
| Y | 12345 | 5AAA | -550 | Out |
How do we match the entries that can be matched off with another Source only, and leave the -250 Y Source entry to be the only Out instead of all 6 records?
