Hi,
I'm new to Alteryx and would really appreciate if someone could provide solution to below:
I have 2 data sets, one has all the debit transactions and one has all the credits. I have used the join tool to combine both data sets. All debits and credits must match and if the corresponding debit or credit is missing for the Customer ID, that unmatched transaction (break) should be displayed in a separate tab of the spreadsheet.
In below example, there are 3 transactions for the same customer (Customer ID = ABC123), but for the 3rd transaction which is in bold font ($100), there is no corresponding debit. So this will be the break (unmatched transaction) and I want to see it in a different tab (as shown in Desired State). Similarly, Customer ID ABC125 also has one break which should be listed in a different tab. Please advise how can I achieve that. I just want to separate the unmatched transactions (breaks) from the joined data set.
Current Data:
Customer ID | Amount |
ABC123 | $100 |
ABC123 | -$100 |
ABC123 | $100 |
ABC124 | $50 |
ABC124 | -$50 |
ABC125 | $500 |
ABC125 | -$500 |
ABC125 | $500 |
Desired State (Breaks):
Customer ID | Amount |
ABC123 | $100 |
ABC125 | $500 |
Solved! Go to Solution.
This approach would not work for this data set. Could you please provide the workflow using this data? Thanks
Current Data:
Customer ID | Amount |
ABC123 | $100 |
ABC123 | -$100 |
ABC123 | $100 |
ABC123 | $150 |
ABC124 | $100 |
ABC124 | -$100 |
ABC125 | -$500 |
ABC125 | -$500 |
ABC125 | -$500 |
Desired Output Data (Unmatched Cr/Dr transactions):
Customer ID | Amount |
ABC123 | $100 |
ABC123 | $150 |
ABC125 | -$500 |
ABC125 | -$500 |
ABC125 | -$500 |
You're the best! Thank you so much. I have large data sets with over 5000 transactions happening on daily basis. Applied this concept and it works perfectly!