Dynamic Sum/Match

[TFish]

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?