How to transfer data from multiple files and to a main Excel file via mapping file

[D_Y]

Hi everyone

First time posting so looking forward to engaging this community!

I have a set of Excel files, with all of them in a format similar to this after performing various data manipulation on Alteryx to look for the latest date for each Product:

File 1:

Date	Product 1	Product 2	Product 3
8/31/2021	$10	$5	$2

File 2:

Date	Product 4	Product 5
8/31/2021	$6	$15

I am looking to transfer the info under each Product to a main Excel file via a mapping file. The mapping file will look something like this:

Product 1	Product A
Product 2	Product B
Product 3	Product C
Product 4	Product D
Product 5	Product E

The first worksheet of the main Excel file looks like this (note that this into does NOT start on cell A1, this is somewhere in the middle of the first worksheet)

Product B	?
Product D	?
Product A	?

What is the best way of solving this? Please let me know if any questions or I can provide more context anywhere.

Thanks a ton for your help! 

[DawnDuong]

hi @D_Y 

Based on the generics, I think you can try something like this:

1) Firstly, since you need to map the "product", it is most efficient if you can transpose the data into a "thin and long" format, where the Product 1, 2, 3 etc... is transposed into a single column.

2) Use a batch macro to repeat step 1 for all the files you need to process. The output should be a master file that has only 3 columns for "Date", "Product", and "Values".

3) Output in step 2 can be mapped with Join Tool.

4) It then becomes straightforward to get the main output you need. On writing to a specific cell location, you can find out more details in the Output Tool help. https://help.alteryx.com/20213/designer/output-data-tool

Dawn.

[D_Y]

Hi Dawn

Thanks for your help!

1. was pretty straight forward so was able to do that.

For 2. I tried building a batch macro based on another user's example, and I tried putting an Output tool at the end of the macro, however I only see the result for one of the files in the output.

In addition, how would you connect the Output tool with a Join tool for 3.? Apologies if my questions are a bit novice as I'm still learning the tool.

Really appreciate your help and let me know if I can provide more context. 

[D_Y]

Hi 

An update to the above post. 

I figured out how to create the output file from the batch macro. However, I'm unsure about how to get the results of the output file to the main Excel file based on mapping. 

Each worksheet in the output file (so the output file created by the batch macro) looks roughly like this:

	Name	Value
9/30/2021	Product 1	$1
9/30/2021	Product 2	$2

The mapping file in this case would be:

Product 1	Product A
Product 2	Product B

How do I get these value to be sent over to the main Excel file, so that the main Excel file appears like this?

Product A	[value from original file goes here, e.g. $1]
Product B	[value from original file goes here, e.g. $2]

Appreciate any help, thanks! 

[DawnDuong · Accepted solution]

hi @D_Y 

Unfortunately I do not have access to Alteryx software at the moment so bear with my generic descriptions instead.

Looks like you already got through steps 1 and 2. 

The next step, you use a Join Tool (https://help.alteryx.com/20213/designer/join-tool)

- Connect the output of the batch macro in step 2 to the "L Input" of Join Tool

- Connect your mapping file to the "R Input of the Join Tool.

- Configure the Join Tool to match based on column "Name" of the L input and the first column (whichever name it is, that contains the values Product 1, 2, etc...). 

The Join output is basically the same as Excel v look-up.

If you are not familiar with the Join Tool, do check out the Interactive Lesson "Vlookup with Designer" (https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201). In fact, the Interactive Lessons would be the first place I recommend for anyone new to Alteryx!

Dawn.

[D_Y]

Hi Dawn,

Apologies for the late reply. I got the workflow to work based on your guidance and a bit of trial and error.

Ultimately yes, I did use the Join tool to match the two files together, then used the Union and Sample tool to update the original file with the newest values.

Thanks so much!