Vertical Merging

One Star

Vertical Merging

Hi ALL,
My source IS AS below.
Column1 Column2 Column3
A 123 111
A 124 565
A 23 699
B 236 125
B 458 89
B 796 36
C 658 988
C 986 475
C 351 632
C 265 677
My output should be as below
Column1 Column2 Column3
A 123 111
124 565
23 699
B 236 125
458 89
796 36
C 658 988
986 475
351 632
265 677
I want this output in excel file.
Kindly assist.
Thanks,
Saty.
Moderator

Re: Vertical Merging

Hi id4dsc,
To make it clearer to you, I have desgined a Job for you. The work flow is that:
tFileInputDelimited-->tMap-->tfileoutputExcel-->tlogrow
-->tjavaRow
The expression in tMap
(String)globalMap.get("last_value")==null?row1.c1:(((String)globalMap.get("last_value")).equals(row1.c1)?"":row1.c1)

For more details, please see my screenshots.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Vertical Merging

Hi Sabrina,
I've tried as you shown in the job. Nut it is throwing an error as below.
Exception in thread "main" java.lang.Error: Unresolved compilation problem:
a21 cannot be resolved to a variable
at asec.sc_6_0_1.SC_6.tFileInputDelimited_1Process(SC_6.java:1434)
at asec.sc_6_0_1.SC_6.runJobInTOS(SC_6.java:1763)
at asec.sc_6_0_1.SC_6.main(SC_6.java:1631)
Kindly assist.
Thnx,
Saty.
Community Manager

Re: Vertical Merging

Hi Saty
It is a compilation error, there must be a wrong setting in the job, we usually open the Code tab and see which line has the error.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Vertical Merging

Hi Shong,
what is the meaning of the below command/function.
globalMap.put("last_value",input_row1.C1);

Kindly assist.
Thnx.
Community Manager

Re: Vertical Merging

Hi Shong,
what is the meaning of the below command/function.
globalMap.put("last_value",input_row1.C1);

Kindly assist.
Thnx.

This code is used to store the last value of c1 column to a global variable. You will see global variable is used very often during your development later, the expression is:
globalMap.put("key",value)
Afterwards, you can get this value by:
(String)globalMap.get("key") //for the string value
or
(Integer)globalMap.get("key") //for the Integer/int value
or
(java.util.Date)globalMap.get("key") //for the Date value
and so on.
Hope this explanation is clear for you.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Vertical Merging

Hi Shong/Sabrina,
I got the output. I've made below change.
globalMap.put("last_value",input_row1.C1); -------------> globalMap.put("last_value",row1.C1);

I've just removed ' input_ ' from the above expression.
I want to do something more than this with this output . In the excel file first row is showing as ' A '. 2nd and 3 rd are empty under C1 column. Instead of that can we merge 3 rows under C1 and show it as ' A ' .
And same for 'B' and 'C' as well.
Kindly assist.
Thnx.
Moderator

Re: Vertical Merging

Hi id4dsc,
I want to do something more than this with this output . In the excel file first row is showing as ' A '. 2nd and 3 rd are empty under C1 column. Instead of that can we merge 3 rows under C1 and show it as ' A ' .
And same for 'B' and 'C' as well.

We have a little confusion on your further requirement, would you mind providing your screenshot about the expected result?(the screenshot of excel file). Do you mean "Merge cells"?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Vertical Merging

Hi Sabrina,
Yes. My requirement includes merging cells.
Below is the screen shot.
Kindly assist.

Thanks,
Saty.
Moderator

Re: Vertical Merging

Hi id4dsc,
As we known that Talend is a code generator ETL which use JAVA as the underline technology generated to perform the Data Extraction, Transformation and Loading. What we can do is loading your processed data into excel without "merge cells". I think you should do it by manually.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.