How to pick a transaction where a reversal happens first and then sale

[chinu267]

I have a list of VINs say 100k odd transactions. If you closely look for the 3 scenarios, for the first two sale happens then reversal in order of time. This is a valid reversal and totals out to ZERO. For the last VIN, reversal happens first and then sale in order of time. In this case though it rolls up to zero, it should be taken as a sale as technically reversal can not happen until sale happens. How will I design a workflow to pick these scenarios? Say for the third VIN, I should only pick one sale and not two sales. Here the valid sale will be "Feb/01/2019 WF04WAGCC4GS55555 34170" and rest should be discarded.

Sale Month         VIN        Ledger Amount

Nov/01/2017 MAJAWAMRKAGT88888 50936

Dec/01/2017 MAJAWAMRKAGT88888 -50936

Dec/01/2018 MAJAWAMRKAGT88888 50936

Jan/01/2019 MAJAWAMRKAGT88888 -50936

Feb/01/2018 WF03WATTG3GT99999 96188

Mar/01/2018 WF03WATTG3GT99999 -96188

Dec/01/2018 WF03WATTG3GT99999 96188

Jan/01/2019 WF03WATTG3GT99999 -96188

Oct/01/2018 WF04WAGCC4GS55555 -34170

Dec/01/2018 WF04WAGCC4GS55555 34170

Jan/01/2019 WF04WAGCC4GS55555 -34170

Feb/01/2019 WF04WAGCC4GS55555 34170

[bb213]

What about Dec/01/2018 WF04WAGCC4GS55555 34170?  

So you only want one output per VIN? It would help if you shared your desired output. 

[CharlieS · Accepted solution]

Hi @chinu267 

It sounds like the result you're looking for is a report on each VIN that shows the final ledger amount based on the logic you specified above. I created the attached workflow that achieves this. Let me know if this helps.

[bb213]

Let me know if this is what you were looking for. I designed a workflow that uses the Summary tool's "First" and "Last" feature based on whether the amount is positive or negative. Should be good to go!

[chinu267]

Thanks for your kind help and support. Using your solution, I manged to find 3 such VINs out of 90K records. Will further investigate and get back if I am stuck with any other issues

[chinu267]

Hi There.

I looked at the workflow output which is below:

VIN               Sale Month Ledger Amount
MAJAWAMRKAGT88888 2017-11-01 50936
WF03WATTG3GT99999 2018-02-01 96188
WF04WAGCC4GS55555 2019-02-01 34170

But. the desired Output is only below:

VIN               Sale Month Ledger Amount

WF04WAGCC4GS55555 2019-02-01 34170

[bb213]

I think this is what you're looking for.