Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Identify invoices with same gross amount and similar invoice numbers

Highlighted
7 - Meteor

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

Highlighted
Alteryx Partner
Alteryx Partner

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. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
7 - Meteor

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?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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