Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Debit Credit Transaction Reconciliation - List Unmatched breaks in different tab

Muhammad
7 - Meteor

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 IDAmount
ABC123$100
ABC123-$100
ABC123$100
ABC124$50
ABC124-$50
ABC125$500
ABC125-$500
ABC125$500

 

Desired State (Breaks):

Customer IDAmount
ABC123$100
ABC125$500
4 REPLIES 4
ponraj
13 - Pulsar

Attaching sample workflow with the given sample data.  

 

Debit Credit Transaction Reconciliation.PNG

Muhammad
7 - Meteor

This approach would not work for this data set. Could you please provide the workflow using this data? Thanks

 

Current Data:

Customer IDAmount
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 IDAmount
ABC123$100
ABC123$150
ABC125-$500
ABC125-$500
ABC125-$500
ponraj
13 - Pulsar

Here is the workflow with your new sample data. 

 

debit or credit.PNG

Muhammad
7 - Meteor

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!

Labels