Find Negative Records That Do Not Have a Matching Positive Record by Address

[gloftis1971]

Hello,

I am trying to complete this process but am not sure how to make it work in Alteryx.

1. Find all negative values in the "item amount" column
2. When a negative value is found, search all records with an invoice data equal to or earlier than the date of the negative value
3. Look to see if any of these records have the same address as the negative record
4. If they have the same address, look to see if the item amount is equal to the absolute value of the negative record

I need to extract the Invoice data and item amount for any negatives that do not have a matching positive record for the same address. Any records that are negative but DO have a matching positive record for the same address can be left in the data.

Any solutions on how to create a workflow for this? Thanks!

[apathetichell]

You would do this in Alteryx like this:

1) filter for a negative amount in item amount (ie [item amount] < 0])

reroute - using Alteryx you would skip step 2 and go to steps 3 & 4 next.

2) using the true anchor from this - you would create an absolute value field.

3) you would join your true anchor from step 1 on your original dataset - joining on absolute amount to amount and address (assuming address is generated by something that isn't a human)

4) using your joined data - you can now filter between the accounting date from your left datastream (negative data) and your right data stream (full datastream) to find which entries in the full are the same or earlier than your accounting date from the negative data

[cjaneczko]

There are multiple Rows of data in that sheet that have $29 and the same address. How do you determine which invoice is the correct one for the line? What if there were multiple orders from the same address for the same amount?

[apathetichell]

@cjaneczko good questions - Ideally there would be a unique reference to match but presumably the amounts, addresses and dates provide a close to unique match. If not perhaps this does 95% of the work and @gloftis1971 can dig into the 5% dupes. @gloftis1971 forgot to mention - I'd recommend swapping your amounts to vstring vs numbers for joining to prevent decimal match issues.

[Diemm]

Did you were able to solve this? i need to do something similar where i need to add a tag in a new column to the records that have a positive amount that matches with a negative amount, and leaving in blank the new column where there is only a positive amount that does not match with a negative amount