Lookup on multiple excel files with one excel file

[VEDH]

Hi,

I am quite new to Alteryx, and need help on the project currently working on where we are replacing an existing excel macro with Alteryx. The macro is currently being used to filter out matched payments.

We have our ledger file in excel which represents all the matched and unmatched payments. Ledger would just reflect matched payments as matched rest are shown as blank. Daily 6 different custodies send the statement in excel file for confirming the payments. Custody's statement are compared with our ledger to figure out the unmatched.

How does the macro works:

1. From our ledger data, 4 columns (Fund, SEDOL, Payment Date and Net Amount) are combined together in a single cell for every row on Sheet 1. Refer below snap from ledger sheet.

Formula used to combine them was =B2&C2&D2&E2

Once the value was received, the formula was removed to get a string value. (Note that the date got converted to 44278)

2. The custody statement will also consist of the same data – Fund, SEDOL, Payment Date and Net Amount. Hence, macro would combine the same for custody data as well on column 1 in remaining 6 sheets respectively (as there are 6 custodies).
3. Then, with the help of V-lookup on each custody sheet the status would reflect as matched if the status was matched on the ledger sheet.

I have already created a half workflow on this where I’ve imported all the data to Alteryx from different sources, cleaned them (as the files were in different formats of excel with different headers and column position). Now I wanted to know is there any better approach to get the job done or shall I proceed with the same approach as was used in existing macro. If the same approach as the macro is to be used, then how to join or find and replace 1 file (ledger file) with multiple files (custody files).

The output must be in a single excel file. But should be in 6 different sheets as per custodies.

Due to data confidentiality I could not send the exact data. Hence, attached dummy sample data which I created myself.

[MattBSlalom]

First of all, I appreciate the amount of detail you've provided describing your scenario, it was very helpful to understand.

For your 6 custody files, we'll want to change the configuration to Output File Name as Field so that we can track which row came from which file.  Next, we'll Union all those rows together (since you've gotten them all formatted the same).  Then I've added a Unique tool to provide just 1 row per unique set of Fund, SEDOL, Payment Date and Net Amount values since I don't know the consequence of having multiple Custodies return the same Ledger entry.

Now we'll perform a single Join tool between the Ledger and this combined set of Custody rows with the Join configured on the 4 fields that would've been concatenated in your previous solution (Fund, SEDOL, Payment Date and Net Amount).  The rows coming out of the L anchor will the the Ledger rows that did NOT find a match in the Custody data set so we'll update the Match Status to null/blank.  The rows coming out of the J anchor will the the Ledger rows that DID find a match in the Custody data set so we'll update the Match Status to "Matched".  Finally, we can Union those 2 result sets together to get back the full Ledger list with the updated Match Status values.

Unfortunately, I don't understand this statement:  "The output must be in a single excel file. But should be in 6 different sheets as per custodies."  How do you determine what ledger rows should go to each Custody?  I assume all the matched ones would go to their respective Custody, but what about the unmatched rows?  Seems like you'd need a 7th sheet to capture those rows...

[VEDH]

Hi @MattBSlalom

Thanks a lof for picking this up.

Here, we are only considered with the custody data which is not stating matched  on ledger. Post this processing, the team would get to know what are the unmatched breaks so that they could work on them to match it off.

So in the join tool instead of taking data from L and J, I would take data from J and R. I believe now you would understand that I am finding from custody data and replacing from ledger data.

I know it’s the opposite way. However, this is how the excel macro was running.

With regards to output, I can assign a column with custody name before the custody data is flowing to union tool. But, now sure how I will split it up back to 6 files or 6 sheets of 1 excel file.

[MattBSlalom · Accepted solution]

In that case, I've removed the Unique tool so we keep those transactions that came through multiple Custody files to put them into their respective sheets.  I also switched the L & R input anchors on the Join to keep from having to rename the fields to keep the Custody versions instead of the Ledger version (therefore, I'm still using the L & J output anchors).

To get the multi sheet output as desired, we add a Formula to generate the output paths (including sheet names as Custody names/numbers).  Then configure the Output tool to Take File/Table Name From Field & select "Change Entire File Path".

[VEDH]

Hello @MattBSlalom 

Much appreciated. Thanks for the help.