Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Identify invoices with same gross amount and similar invoice numbers

byagelski
8 - Asteroid

Hello - I'm trying out an audit use case to identify potential duplicate paid invoices.  The pattern I'm looking for would be the same vendor name, same gross amount, similar invoice #.  Specifically with the invoice number, I sometimes see invoice numbers adjusted with 'A', 'B', or '-1' entered at the end of the invoice.  Thus invoice # 1234 would also show as 1234A or 1234-1.  My initial thought was to try and verify that for any vendor and gross amounts that match, compare the invoice number with "contains", thus 1234 is contained within 1234A and 1234-1. 

 

I am trying the WildCard Join macro available within the Community, but what I'm finding is that pushing a lot of data through that is quite time consuming for the workflow. 

 

Anyone have a different solution?

 

Here is some sample data

Vendor       Inv Date     Invoice #     Gross Amount

Abbot        2019-01-01   111             2000
Abbot        2019-01-01   111A            2000
Abbot        2019-01-02   111B            2500
Abbot        2019-01-01   1234            2000
Reynolds  2019-01-01    111               2000
Carrie       2019-02-02    7777            1500
Carrie       2019-02-02    7777-A         1500
Carrie       2019-02-03    7777-1         1500

4 REPLIES 4
joshuaburkhow
ACE Emeritus
ACE Emeritus

I just built this batch macro this morning that will do the append but on a row my row basis (therefore becoming more performant ;)). This might help you or at the least give you an idea of how to approach. The main idea here is to join the same table to each other and match based on your needs. 

Joshua Burkhow - Alteryx Ace | Global Alteryx Architect @PwC | Blogger @ AlterTricks
JosephSerpis
17 - Castor
17 - Castor

Hi @byagelski I mocked up a workflow that flags potential duplicates. Let me know what you think?Duplciates_210819.PNG

byagelski
8 - Asteroid

Pretty slick, thank you so much!  I've been a little shy to take on Reg Ex.  Looks like it identifies as a duplicate even if I put A in front of the invoice number.  Any recommendations on sites for learning more about the Reg Ex syntax and formulas?

JosephSerpis
17 - Castor
17 - Castor

Hi @byagelski Alteryx have some interactive lesson using Regex I would recommend.

 

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons?lessons=all

Labels