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

Identifying Invoices that net zero

Gr4c3Sult
8 - Asteroid

Hi All,

 

I need to identify all invoices that Net zero and was wondering if any of you had an idea on how to get this done in Alteryx.   I tried trimming CR and then sorting by invoice number, but that doesn't identify when they net zero.  There are over 500K+ rows of data, so eyeballing it would be awful.

 

An example:

 

CSPIN00602461  1000.00

CSPIN00602461CR -1000.00

 

Any help or suggestions is greatly appreciated.

 

Thanks!

Grace

6 REPLIES 6
atcodedog05
22 - Nova
22 - Nova

Hi @Gr4c3Sult 

 

Bit confused what your input data looks like. Guessing its table with 2 columns (order and amt). You can use summarize tool to get sum and add it back to main data.

 

Workflow:

atcodedog05_0-1627064098156.png

 

1. Using formula to Trim "CR"

2. Using summarize groupby order, sum of amt to get net.

3. Using find and replace tool to add net column to main data.

 

If this is not as expected. Please provide sample input and expected output in a sample file.

 

Hope this helps : )

Gr4c3Sult
8 - Asteroid

Forgot to add that I also need to confirm the vendors are the same.

 

Vendor     Invoice                     Amount

12345      CSPIN00602461        1000.00

12345      CSPIN00602461CR -1000.00

 

atcodedog05
22 - Nova
22 - Nova

Hi @Gr4c3Sult 

 

Just add vendor also in summarize groupby and they will also be considered.

 

Workflow:

atcodedog05_0-1627064695968.png

 

Hope this helps : )

Hi @Gr4c3Sult 

 

I took an assumption that both the invoice number and amount will be in the same cell. You can modify this if need be. 

 

Using Fuzzy Match with Make Groups you can achieve your end goal. Please see below. I added another account to test the logic. It seems to work for the invoices you provided.

 

Hope it helps. Cheers!

christine_assaad_0-1627064634002.png

 

Gr4c3Sult
8 - Asteroid

Thank you!  That works 🙂  Do you have any ideas on how to identify Invoices with different invoice numbers but Net zero?

 

Thanks Grace

Gr4c3Sult
8 - Asteroid

@christine_assaad   Thank you for your help.  I ran this with my original data set and there are so many invoice numbers that are similar that the match returned results were enormous and hard to find the net zeros.  

 

The above solution works for most of the net zeros.  

 

Thank you again for your time and help 🙂

Labels