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

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!

Output

Join

Accepted answers

DawnDuong

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.

All comments

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

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

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.

Dawn.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703