Data import from multiple excel file with specific format

[LFLee]

I've creating a work flow to import data from all the different spreadsheets onto one spreadsheet with specific fields. The files that I'm looking at inputting is as follow: -

Excel 1: the headers of the file that the data from subsequent file should be input to.

Excel 2: Data under first line of the Consolidated column of each individual to be mapped to header (A) in Excel 1, second line of the Consolidated column of each individual to be mapped to FIN no field in Excel 1

Excel 3: Data set with information with the difference between columns,  A and B, and, C and D to be mapped to column G of Excel 1, and column J of Excel 1respectively.

Excel 4: Data set with information with the difference between columns A and B to be mapped to E of Excel 1.

I've tried to union the data in Excel 2 with the respective headers in Excel 1 but is unsuccessful. To illustrate the above, I've attached my workflow and the screenshot of Excel 1 and 3 for reference.

Appreciate if anyone can provide a fix to the workflow? 

[LFLee]

Hi @TonyA , I've tried to create the work flow based on your suggestion however, it is not delivering the result that I need in the output matrix that I've shared in my earlier message.

I've attached the workflow below and appreciate if you can help to provide a fix for me to get the desired output.

[TonyA]

A few things:

• I don't have two of the tables you are referencing: Bonus YA2015 to YA2017.xlsx and Exchange Rate.xlsx
• The Cleaned Data table doesn't have a Consolidated column. You are creating one that is all zeroes, but that doesn't help extract the data. We need a table that is consistent with the instructions in the Final Output Matrix file.
• Unless there is something in the two missing files that provides a relationship, there is no way to join the names in Cleaned Data to any column in any other provided table. 
• Finally, the way I would suggest doing this is:
  • Clean up each of the inputs (leave one dummy row in Output Matrix)
  • Join all the data set except Output Matrix
  • Join the data set from the previous step with Output Matrix on position
  • Use formulas to populate the fields from Output Matrix using data from the other fields
  • Remove all fields except the ones that came from Output Matrix

If you can send me the Bonus file, Exchange Rate File and a Cleaned data file with a populated Consolidated column and some way of tying Names in the Cleaned data to Personnel Numbers or the Name code in Excel 3, then I can take another look at this.

[LFLee]

Hi @TonyA , please find below the Bonus YA2015 to YA2017 file, Exchange Rate and Cleaned YA 2015 data file. There is no consolidated column in the Cleaned YA2015 data file. I've created that column using the Multi field Formula.

What I'm trying to achieve in the workflow is to import the data in the Cleaned Data file to the fields in the Output Matrix, and then import  the personnel number by matching the FIN number in the Cleaned Data file against the Bonus YA 2015 to YA2017 file, which contains the name, FIN number and personnel number.

Thereafter, I'll import the bonus amount in Excel 3 and 4 to the output matrix by matching the personnel number that has been imported to the output Matrix file. Last but not least, for the exchange rate, I will match the currency code from excel 3 to the exchange rate file and match it to the respective currency code.

I'll need all the fields for each employee to be on the same line instead of what it looks like now. Also, I have data on each tab of the excel file so the final work flow will need to run on all tabs.

[TonyA · Accepted solution]

I think I finally sorted this out. I've attached a workflow that gets you most of the way there. There are fields in the output matrix that weren't defined, so a lot of the calculations couldn't be done, but I set this up so you have all the available data in one place and once you get the remaining data, the calculations should be easy from there. This turned out to be a much larger project that I expected. Normally for a full project like this you should be working with a local partner or your local Alteryx team. On Community, we generally help with more specific questions like why a tool isn't working as expected or how to do a specific function or task.

Look through the attached workflow. I made a number of assumptions that might not be correct, so I expect there are things you will need to tweak. If you have specific questions about certain techniques I used or how a tool works and you can't find help in the product documentation or in the existing community content, then please post your question. But I think you have a good start with this workflow. There are several columns in the output matrix, specifically J, K, M, and O, that have no instructions or the instructions are either unclear or incomplete. I think that if you sort through those, you can finish this pretty quickly.

[LFLee]

@TonyA , thank you so much for solving the workflow for me. I have only one question. For the FX rate 1, I would need it to show the rate instead of the buy rate, so do I use a select function after the filter for Exchange rate?

Appreciate your support on this.

[TonyA]

It looks like I made a mistake there. I should have had a rate value as the FX Rate 1, not ccy.

You should change the column selection inside the join tool.

Looking at this section of the workflow:

You want to change the column selection in the join tool to the column you want to put into FX rate 1 and then change the name of the column accordingly. So if you wanted to use Mean Rate for FX Rate 1, you'd configure the tool like this:

Just make sure everything from the Right input is unchecked except for the rate you want and rename that rate to "FX Rate 1". If the name is wrong it won't union correctly later.

I added an updated version of the workflow. I made the change but also fixed a minor issue with Personnel No. I only included the workflow and not all the source files.

By the way, please mark my last response as a solution if it has helped you to resolve this.

[LFLee]

Thank you so much for solving this @TonyA . Really appreciate your help!

[LFLee]

Hi @TonyA , sorry for the multiple questions but I've got one last question. I've run the workflow on my actual data and i only got output for one file. I've got the error in the screenshot. Can you let me know if I've missed any files as I merely replaced the 5 input files?

Appreciate your help on this.

[TonyA]

Check the second input file. It looks like Personnel no has a different name or is missing from the file. If the name is different, you should be able to pick the correct name from the dropdown list. If the name is missing, you'll need to either figure out how to get it in the file or find something else to join on.

[LFLee]

Hi @TonyA , the personnel number is there for both the performance bonus people and PAYG bonus people. I've attached the screenshot below.