Alteryx Designer Discussions

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

Reconciliation Logic without using Multi-Row formula

vijaylnyadav
8 - Asteroid

Hi, 

 

I have to define the workflow for reconciliation please help me in this.

 

Condition: - 

1. We cant use Multi-row formula.

2. First it will check for same check number in the same column (Check Number1), if it is found then it will search for same amount present in same column(Amount1) with opposite sign then it is reconciled and deleted from the data.

For e.g.

102A102-200  
102A102200  

the above data is reconciled.

 

3. Then, it will check for same check number in the same column, if it is found then it will search for same amount present in another column(amount2) with same sign then it is reconciled and deleted from the data.

For e.g.

102A102-200  
102A102  -200

the above data is reconciled

 

 

Input Data: - 

Check Number1EMPLIDAmount1Check Number2Amount2
101A101-100  
103A103-300  
102A102-200  
102A102  -200
101A101-100  
102A102-200  
102A102200  
103A103-300  
103A103  -300
103A103300  
103A103-300  
104A104400  
104A104-400  
104A104  -400
105A105-500  
106A106-600  
106A106  -600
106A106  -600
107A107-700  
107A107  -700
107A107  700

 

 

Desired Output: - 

Check Number1EMPLIDAmount1Check Number2Amount2
101A101-100  
101A101-100  
102A102  -200
103A103-300  
104A104  -400
105A105-500  
106A106  -600
107A107  700
16 REPLIES 16
atcodedog05
21 - Polaris

Hi @vijaylnyadav 

 

Just curious why no use of multi-row formula tool?

vijaylnyadav
8 - Asteroid

Thanks @atcodedog05 for your reply/

We cant use it because it is the business requirement. We cant use it anyway.

Please suggest some another workflow if possible by alteryx.

john_watkins
10 - Fireball

Sometimes when things are complicated with a MultiRow formula, they can be done simply with a few joins.  This is good when the datasets are variable or just writing the formula would be a nightmare.  I usually put a RecordId on the records before I start this stuff to make it clear what is going where.   Join the data back to itself on CheckNumber and possibly EmpNo (depending on your rules)   You will get a RECORD and a TEST record and I sometimes rename the records so I can tell them apart.  You can then test using simple IF statements.  Flag the records and use a FILTER to remove or keep what you want.

 

If 

vijaylnyadav
8 - Asteroid

thnx @john_watkins for reply.

 

 

I tried this way also but i can achieve this because i am stuck with which join to use. If you can explain with some workflow it will help a lot.

Christina_H
11 - Bolide

Not the most elegant solution but I think this works.  It doesn't give exactly your output but I think you might have missed something, e.g. as far as I can see all entries for A102 cancel out, but one is left in your output.

vijaylnyadav
8 - Asteroid

thanks @Christina_H .

 

Yea i have missed that. Thanks for noticing it. I will check the solution now and will revert you shortly

vijaylnyadav
8 - Asteroid

Thanks @Christina_H it worked for me and hence marked as solution.

If in future if something went wrong then i will contact you via this post.

 

 

Thanks a lot.

vijaylnyadav
8 - Asteroid

I would like to thank @Christina_H for providing this excellent solution.

This workflow have a 100% accuracy.

You are genius. I have become huge fan of your Alteryx knowledge.

 

Thanks a lot.

vijaylnyadav
8 - Asteroid

Hi @Christina_H ,

 

I have found one bug with this workflow.

Please see the workflow attached.

 

if we have 4 records of Check number say 101 which has two -300 value in amount1 column and two -300 value in amount2 column.

 

According to the condition it should be reconciled but they are not reconciled. Please help me on this.

Labels