One Star

[resolved] Excel transformation

   I have 10 excel file(one for each year from 2005 to 2014 ) with the same structure as the first one(input), everyone contains the number of male, female and total that live in a city, for each single year of age(0,1,2...100) (there are the same 30 cities for all files) .
I have to merge all the input file in one output that contains 6 columns : city/age/m/f/t/year like in the figure.
Some suggestion?What component should I use to make the transformation and the merge?
Thanks to all! Smiley Happy
Marco
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] Excel transformation

Thanks it works! 
Just one more thing... I put all the input files in a directory, so my job is like:
tFileList--> tFileInputExcell-->tMap--->Toutput   setting city as key...But the tMap, insted of add every file to the out, substite every file. The result is that in output I have only the data about the last file(2014)... Where I am wrong? Smiley Frustrated 

edit I haven't checked "Append existing file" ahahah resolved
2 REPLIES
Seventeen Stars

Re: [resolved] Excel transformation

I suggest you use a tMap with an normal output (out0) with your output schema and add a joined output (out1) to it.
If you create an output in a tMap you will always ask for the kind of output, here choose joined output to build out1.
First map the m f t columns from the 0 section to the out0 columns (and all other too) and set as age fix 0
Next map the m f t columns from the 1 section to the out1 columns (and also all others too) and set as age fix 1.
A joined output sends its output to the physical output (out0). Its like a union.
One Star

Re: [resolved] Excel transformation

Thanks it works! 
Just one more thing... I put all the input files in a directory, so my job is like:
tFileList--> tFileInputExcell-->tMap--->Toutput   setting city as key...But the tMap, insted of add every file to the out, substite every file. The result is that in output I have only the data about the last file(2014)... Where I am wrong? Smiley Frustrated 

edit I haven't checked "Append existing file" ahahah resolved