Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Data import from multiple excel file with specific format

LFLee
8 - Asteroid

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? 

 

 

 

22 REPLIES 22
TonyA
Alteryx Alumni (Retired)

I'm assuming from your example that you only want to address steps 1 and 2 and populate Name, FIN, and (A). 

 

There are a couple of interesting aspects to the data that I note in the attached workflow. The trickiest part is that you have two rows with header data and the first row has merged cells that have to be applied to multiple headers. I highlighted the section where I handled that. I think you should also review how the Crosstab tool works and investigate the Dynamic Rename tool for changing field names. Take a look at the workflow and I would also suggest you check out the following links:

 

Good overview of Alteryx tools for Excel users that discusses crosstabs: https://community.alteryx.com/t5/Videos/Intro-to-Alteryx-for-Excel-Users/m-p/45398

Crosstab Tool Mastery: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Cross-Tab/ta-p/4368

Dynamic Rename Tool Mastery: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Dynamic-Rename/ta-p/32...

 

 

 

LFLee
8 - Asteroid
Hi @TonyA, thanks for sharing. I need to address all points including the import of excel 3 and 4 into the respective columns in Excel 1 apart from steps 1 and 2 and populate the name and FIN.

How do I go about doing that? I was trying to do it with the join multiple function but I can’t get the results I want.

Any suggestions on how to do it?
TonyA
Alteryx Alumni (Retired)

The only cleaned data you provided is for Name and Consolidated. The data in the other sheets of Output Matrix don't help since they don't contain data for (B) and the names don't line up with the cleaned data anyway.  Can you send a more complete input data set so I can see what you want to do? 

TonyA
Alteryx Alumni (Retired)

 If you have a data set that shows the (B) values for each name you should be able to join it to the set from my workflow using the Name as the join key. You can add other columns from other data sets in a similar way. You can use the multiple join function if you have the name column in each of the sheets and then arrange the fields in the "select" section of the Multijoin tool.

LFLee
8 - Asteroid

Hi @TonyA , I've attached the two files (i.e. Excel 3 and Excel 4) on the input data to be included in the work flow to generate the output. I've also attached another file, which is the Exchange rate file, which is to be matched to Column J in Excel 3 and mapped to the fields FX rate headers in Excel 1.

 

Appreciate your help on this as this is a huge number of files that I'm dealing with and I'm not sure how to get the output I need.

TonyA
Alteryx Alumni (Retired)

Thanks for the files, but it still isn't clear how any of this relates to the problem you stated at the start. I think you'll need to take a step backwards and clearly document what you want to do and make sure it ties directly to the example files.The let us know what we can do to help.

I assume you have a well defined process in place and want to implement a similar process in Alteryx. The first step in doing that is to clearly describe the current process in a way that can be understood by people who don't know this data.Then provide a set of sample input data and the output data that is the direct result of running the process on that input data. The key thing we need to understand is how the input data relates to the output data. Once we have that, we can help you create a workflow.

LFLee
8 - Asteroid

Hi @TonyA , apologies for not being clear on the output I need. In short, I have 5 input files from which I want to select the relevant fields to get the output on one excel file.

 

A summary of what each file (file name stated below) contains: 

 

Output matrix: The format and headers of the output.

 

Cleaned Data: contains the name, bonus reported previously, total employment income reported previously and FIN number. The fields, bonus reported previously, total employment income reported previously and FIN number, are ranked in the same order aforementioned under the header, Consolidated.

 

The name is found under the header, File 6, and a further text to column function is to be applied to separate the name and "_YA2015.pdf" into two columns as we only need to name to be transposed to the final output file along with the above 3 fields.

 

Excel 3: Contains information on the Performance bonus and PAYG bonus and currency code

 

Excel 4: Contains the T Bonus.

 

Exchange rate: Contains the exchange rate file.

 

I've attached below how the final output should look like with comments on what is the data from each file that needs to be transposed to the final output. I've also attached the files, Cleaned data YA2015, Excel 3 and Excel 4 again with the sample data for the workflow design.

 

Appreciate any assistance that you can render on this is much appreciated as I can't get the final output I'm looking for.

 

 

LFLee
8 - Asteroid

Hi @TonyA , please find the updated Final Output Matrix with data file, Excel 3, Excel 4 and Cleaned Data file for solving the workflow creation.

 

Appreciate your assistance on this in advance.

TonyA
Alteryx Alumni (Retired)

This data doesn't work either. The Cleaned Data has a totally different format than your original file and does not have the Consolidated column required by the Output Matrix. There doesn't appear to be any way to join Cleaned Data to any of the other sheets -- the only common column is Name and the names don't match.

 

There is nothing to be done until you have consistent data that can be joined. I can give you the general approach to combine this data once you've figured out how to add a column to either Cleaned Data or one of the Excel files that allow you to join them. You will want to join the three files on common keys. First, process Cleaned Data in a manner similar to what I sent you in the pt1 workflow. Join Cleaned data to Excel3 on the new key column. Then join to Excel4 on Personnel No. Join the Exchange rate columns you need based on currency code. Finally, setup the output Matrix as I showed in the pt1 workflow and join it to the combined data set by position.

 

Please get the data sorted out and try to do what I described above. If you can't get it working, please post your workflow here and I'll take a look at it.

 

You'll now have one row for each person with the Cleaned Data columns followed by Excel3 columns, then Excel4 columns then the Exchange rate columns and finally the Output Matrix columns. You can now create formulas to populate each of the Output Matrix columns from the other columns. The last step is to use a Select Tool to remove all the columns except those from the Output Matrix.

Labels