Reconciliation Logic without using Multi-Row formula

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.

102	A102	-200
102	A102	200

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.

102	A102	-200
102	A102			-200

the above data is reconciled

Input Data: -

Check Number1	EMPLID	Amount1	Check Number2	Amount2
101	A101	-100
103	A103	-300
102	A102	-200
102	A102			-200
101	A101	-100
102	A102	-200
102	A102	200
103	A103	-300
103	A103			-300
103	A103	300
103	A103	-300
104	A104	400
104	A104	-400
104	A104			-400
105	A105	-500
106	A106	-600
106	A106			-600
106	A106			-600
107	A107	-700
107	A107			-700
107	A107			700

Desired Output: -

Check Number1	EMPLID	Amount1	Check Number2	Amount2
101	A101	-100
101	A101	-100
102	A102			-200
103	A103	-300
104	A104			-400
105	A105	-500
106	A106			-600
107	A107			700

Datasets

Accepted answers

Christina_H

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.

Reconciliation.yxmd

Christina_H

One final attempt from me using tile tools. Those may still not be accepted by your client. I will also admit that I don't know a lot about using them, so I recommend some thorough testing.

By the way, for the original to work over multiple runs you need to change the input every time to the output of the previous run.

Reconciliation2.yxmd

All comments

atcodedog05

Hi @vijaylnyadav

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

vijaylnyadav

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.

dataguyW

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.

vijaylnyadav

thnx @dataguyW 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

Reconciliation.yxmd

vijaylnyadav

thanks @Christina_H .

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

vijaylnyadav

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

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

Hi @Christina_H. Thanks for your instant reply.

Please help me on this issue because I have to deliver it asap.

Because of only this issue I am stucked otherwise it is a great solution.

Christina_H

For anyone else following this, the problem is where there are multiple possible pairs that can be matched to reconcile. The unique tools end up removing too many records so not reconciling everything.

Unfortunately I can't come up with a solution that doesn't involve either a multi row formula tool or an iterative macro, both of which have been excluded by @vijaylnyadav's client.

The data can still be reconciled if necessary by running it through the original workflow multiple times until no more records are removed. All removed records are correctly reconciled, but not every reconciliation is picked up in a single run.

vijaylnyadav

Hi @Christina_H,

By running multiple times the records are still not reconciled. Every time we are getting the same result.

Please suggest alternate solution if anyone has it.

Christina_H

One final attempt from me using tile tools. Those may still not be accepted by your client. I will also admit that I don't know a lot about using them, so I recommend some thorough testing.

By the way, for the original to work over multiple runs you need to change the input every time to the output of the previous run.

Reconciliation2.yxmd

vijaylnyadav

HI @Christina_H thanks for your reply.

We had spoke to client for using multirow tool.

So we have to show some demo to them about this data.

So can you please help me to redesign the workflow using multirow tool.

This time seeing the issue we think client will accept multi-row tool.

Can you please help me on this.

Thanks for all your support.

Christina_H

Unfortunately I can't spend any more time working on this. My previous solution using tile tools may work for you, but I've provided two solutions now and can't justify spending more time on a third.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703