Search code examples
pentahodata-integration

Pantaho excel input multiple sheet to one sheet

I want to transform my excel input (with multiple sheet) to another excel output but with only one sheet. But in my output file I have one problem with the different data.

This is my input file :

Sheet1

date       data1    data2
01/01/2015  1        9
02/01/2015  2        6
03/01/2015  3        3
04/01/2015  4        2
05/01/2015  5        5

Sheet2

date        data3
01/01/2015  2
02/01/2015  3
03/01/2015  6
04/01/2015  9
05/01/2015  8

and the output file :

date               data1    data2   data3
01/01/2015 00:00    1,00    9,00
02/01/2015 00:00    2,00    6,00
03/01/2015 00:00    3,00    3,00
04/01/2015 00:00    4,00    2,00
05/01/2015 00:00    5,00    5,00    
01/01/2015 00:00    2,00        
02/01/2015 00:00    3,00         
03/01/2015 00:00    6,00        
04/01/2015 00:00    9,00        
05/01/2015 00:00    8,00

But in my case i would like to have this output :

date                data1   data2   data3
01/01/2015 00:00    1,00    9,00    2,00
02/01/2015 00:00    2,00    6,00    3,00
03/01/2015 00:00    3,00    3,00    6,00
04/01/2015 00:00    4,00    2,00    9,00
05/01/2015 00:00    5,00    5,00    8,00

So the date is duplicate and values drom data3 are after values from data1.

Can you help me because I'm new on Pentaho and I don't know how to resolve this problem.

Thanks

Solution

  • You can do it as follows. enter image description here

    In Microsoft Excel Input gets the data of 1st sheet and Microsoft Excel Input 2 gets the data of 2nd sheet. Merge Join you can do the join using date field you obtained from two sheets. You will get the result as expected.