Identify Contra entries with combination of Customer code, Customer GSTN and Invoice No

[Khanzali007]

Hi Friends,

I'm working on an Alteryx workflow and currently need some guidance.

I am trying to remove contra entries from a dataset in Alteryx. Where one amount is a positive entry (a debit) and the other amount which offsets it is a negative (a credit). Is there a quick way to identify these entries & comment as "CONTRA" ? 

For Example: 

In below data set, there are 3 entries with same Customer code, customer GSTIN & Invoice No.

In which 2 set are records were Booked and reversed, and third entries is final entry. Output is highlighted in Red.

Customer code	Customer GSTN	Inv No	Taxable	Contra
40269779	27AADCD0086F1ZW	SI2021-124	65,000	Contra
40269779	27AADCD0086F1ZW	SI2021-124	(65,000)	Contra
40269779	27AADCD0086F1ZW	SI2021-124	65,000

For your ref i have attached a excel file with sample data & his output.

Thank you

[TheOC · Accepted solution]

HI @Khanzali007 

Cheers for the interesting Challenge!

I managed to solve it (I think?) with this horrible horrible if statement in my multi-row formula, although technically its a 1 tool solution:

Basically what I'm doing, is assigning the contra tag if the below line is the same as the current line divided by 1 (which converts it from - to +, or vice versa).

The only other steps are if the row above is contra, I need to assign this row to contra too, as they come in pairs (but ignoring it, if the two rows above are 'contra').

Give it a try on your data and see if it works!

Cheers,
TheOC

[Qiu]

@TheOC @Khanzali007 
Mine is not pretty at all.😁

[vizAlter]

Hi @Khanzali007 — Try this solution:

Please mark this solved if it helps.

[apathetichell]

And another one - just to clarify can you confirm that in a scenario with 3 entries there is no way to tell which of the two positive entries is not marked contra? I'd expect some kind of timestamp so the earlier one would be marked contra - but I didn't see one in  your dataset.

[RaviP]

Hi @Khanzali007 ,

Hopefully the attached solution hepls

[Khanzali007]

First 2 will be a Contra entry. For example i have booked an invoice and then cancelled due to wrong description or material code. Later i have issued new revise invoice with same value but with small rectifications.

Hope it is clear.

[TheOC]

hi @Khanzali007 
I believe my workflow above solves this issue, is this not the case?

Cheers,
TheOC

[Khanzali007]

@TheOC Thanks for the reply. Your solution works perfectly with by dataset provided. When i have applied to the large actual data, there i have seen some of the entries are wrongly mapped. I am just looking to that data and i will get back to you soon. 

I think that is my mistake i might missed any condition or any, let me have look and get back to you.

Meanwhile please help me to understand the below formula you have applied.

IIF([Row-1:Contra] = "Contra", IIF([Row-2:Contra] != "Contra", "Contra", IIF([Taxable] = ([Row+1:Taxable] / -1), "Contra", null())), IIF([Taxable] = ([Row+1:Taxable] / -1), "Contra", null()))

[TheOC]

yeah ofcourse @Khanzali007!

The best way i could think of, to explain this formula, is through fletching it out in a code editor:

Here you should see each step it undertakes, explained in a little more detail. I hope this makes some more sense (?)
If not, just let me know, i'll try to explain it another way, its a tricky one to elaborate 😁

Also, if you manage to create an anonymised dataset with some of the errors in, let me know and ill try to have a look at it, there may be an issue in my logic.

Cheers,
TheOC