Identify Contra entries with combination of Customer code, Customer GSTN and Invoice No
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@TheOC @Khanzali007
Mine is not pretty at all.😁
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
hi @Khanzali007
I believe my workflow above solves this issue, is this not the case?
Cheers,
TheOC
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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()))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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