Finding a Match in Alteryx

[johnson1]

I am a newbie to Alteryx and am trying some use cases from VBA in Alteryx.

We have a process that:

1. Daily I receive 50 attachments from 50 different vendors
2. In VBA I open those 50 workbooks by Looping thru our server that has the 50 attachments
3. Once I open the individual workbook
4. I open a historical workbook of all previously received vendor transactions
5. I scan all transactions from the daily workbook vs. the historical transactions
6. If I find a match between historical and daily then I flag it as a duplicate transaction and load the transaction(s) to a 3rd workbook called Daily Transactions Received today.  If it is not a duplicate then load the transaction(s) to the Daily Transactions Received Workbook.  The Daily Transactions Received Workbook will store all transactions received today for all of the 50 vendors.
7. I then leave OPEN the historical workbook AND the Daily Transaction workbook
8. I then loop to  the next vendor workbook (x of 50) and repeat 3-7

Questions

#1-I am trying to determine from a Designer perspective the best approach ?  Note I do not want to have to open the Historical and Daily Transactions workbook 50 times.

#2-Also we have Alteryx Server - is it possible to save/store the Historical workbook on the server as I would like to reduce latency when grabbing and saving the files.

#3-Am I better off to use VBA or will Alteryx handle this well

[Qiu]

@johnson1 
In order for us to give try. can you

• define the match in 'If I find a match between historical and daily'
• Some sample inputs files would be helpful.

[johnson1]

Hi the below would be a match

Historical File

Invoice Due Date - Dec 1 2020 - Vendor ABC - 25,000 - Tran Type - Poultry

Daily File

Invoice Due Date - Dec 1 2020 - Vendor ABC - 25,000 - Tran Type - Poultry

Invoice Due Date - Dec 2 2020 - Vendor ABC - 35,000 - Tran Type - Poultry

Our existing VBA code would flag the 25,000 record as a potential duplicate and place in an exception queue (tab) within the Daily Received Workbook.

The 35,000 transaction is fine and would not be flagged as an exception.  It would be placed in a tab called Pending within the Daily Received Workbook.  Once processed and approved we would load the 35,000 transaction to the Historical file.