Apply all transformations on Data before merge

[omar_bk95]

Context:  I want to clean my data in a specific way because it has the same structure (different excel sheets).

I have done the cleaning for 1 sheet. it works great as expected. Now I want to merge all the excel sheets together while applying on each one of them the same transformation I did previously (pre-merge). 

In other words: input sheet1->clean->input sheet2->clean same way->append rows below sheet1->input sheet 3->clean same way->append rows below sheet2(and sheet 1 obviously)....

Example in the image is a simplified version without cleaning applied.

 

[atcodedog05]

Hi @Qiu 

Your solution of this post 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Add-table-to-each-row/td-p/675754

is perfect for this scenario can you help out. 🙂

[omar_bk95]

However, I am wondering, how would i do this iteratively over multiple different excel sheets. 

[Qiu]

@atcodedog05 
You really like me, huh?😁

[omar_bk95]

ramii

7 - Meteor

‎07-13-2017 10:05 AM

@david-carnes .Input So lets say there are 5 files with 10 records each.

I want to pick one file, do some data cleaning and preparation. Then pick another file and do some data cleaning and preparation. Do this for all files that I have in a particular directory (5 or 6 depending on month). Finally when i have done the data cleaning and preparation for all files individually. 

I want to merge all of the files together by using a union tool(easy). so i get an output of 1 file with 50 records.

Basically i want to do what i have written in bold once in the workflow but repeat it for all the files. (just like a for loop would execute same instructions on each incoming file in a programming language) I can definitely do it for all the files individually but I dont think it would be an elegant way.

In crux, looking to build a for loop which takes one file at a time and does some actions. When i come out of for loop, i take the modified version of these files and merge them all together.  

Found this in a post, is that doable?

[omar_bk95]

@david-carnes .Input So lets say there are 5 files with 10 records each.

I want to pick one file, do some data cleaning and preparation. Then pick another file and do some data cleaning and preparation. Do this for all files that I have in a particular directory (5 or 6 depending on month). Finally when i have done the data cleaning and preparation for all files individually. 

I want to merge all of the files together by using a union tool(easy). so i get an output of 1 file with 50 records.

Basically i want to do what i have written in bold once in the workflow but repeat it for all the files. (just like a for loop would execute same instructions on each incoming file in a programming language) I can definitely do it for all the files individually but I dont think it would be an elegant way.

In crux, looking to build a for loop which takes one file at a time and does some actions. When i come out of for loop, i take the modified version of these files and merge them all together.  

Found this in a post, is that doable?

[Qiu]

@omar_bk95 
You have 2 input, 2 input2, 2 input3, so you have 6 sheets? and one output?

And seems append happens between the input having same name from your output?

Can elaborate more?

[omar_bk95]

I have 6 excel sheets.

I want to clean them all the same way. Then append them all together to make one big sheet.

Sheet1->clean->sheet2->clean->append below sheet1->sheet3 ->clean->append below sheet 2 (which is below sheet1) etc...

Final output:

Sheet1 cleaned

Sheet2 cleaned

Sheet 3 cleaned

it is like doing a loop in python 

final = []

for i in sheets:

x=clean(i)

final.append(x)

[Qiu]

@omar_bk95 
Made something looks like your output.