community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Need some help on the logic and calcuation.

Asteroid

Hi Alteryx pros,  

 

I am trying to build  a logic, but have no idea how to do it in Alteryx.   

 

At the same customer number and purchase order number level,   I want to off set the credit memo to the invoice if the amount added up are within  the range from -$0.5 to $0.5...  

 

For example, in below example,  all items under same customer 100020 and PO No. 10093 or 10091. The same color highlighted two items are the ones I am looking for with the expected result.  what would be a good logic to set up a calculation in Alteryx to achieve that? My goal is to have an list of potential offset items with below columns ( The original data columns are in the screen shot)

 

 

PURCHASE_ORDERCUST_NOCM _ NO.CM_AmtINV_NO.INV_AmtDiff
1009310000201038065-294.322994972294.23-0.09

 

I have nowhere to start, any help is very much appreciated!     

 

Thank you everyone in advance!

 

Flora

 

 

 

Alteryx.JPG

 

Magnetar
Magnetar

Hi,


I grabbed a couple example records from your file and built a quick workflow to do this.

 

The basic summary of what I did is:

I split out the CM and INV values from the data, and renamed the appropriate columns for each.


I Joined the data together on PURCHASE_ORDER and CUST_NO, and deselected the duplicated fields between both inputs.


I created the "DIFF" column by adding CM_Amt and INV_Amt together.


Then, I filtered for records that had a Diff >= -.5 and Diff <= .5, to find those within your threshold.


This removes the duplicate records created from some payments having the same Cust_no and Purchase_order.

Note that this could still encounter challenges if there are ever multiple invoices/payments with the same purchase order and customer number that are for similar dollar amounts.

 

Hope this helps!

Labels