Six Stars

Data arrangement: multiple rows data in one row

Hi,

 

Here is my requirement

INPUT FILE FORMAT

CONTRACT_NO | NAME | DOB

1 | JOHN | 01/01/2016

1 | SMITH | 05/05/2016

2 | JASON | 06/06/2016

 

OUTPUT FILE FORMAT

CONTRACT_NO | NAME1 | DOB1 | NAME2| DOB2

1 | JOHN | 01/01/2016 | SMITH | 05/05/2016

2 | JASON | 06/06/2016 | |

 

Merge the data with same contract_no into one row

Please let me know which components can be used to achieve the desired output.

Thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars TRF
Nine Stars

Re: Data arrangement: multiple rows data in one row

Here is what you expect.

Capture.PNG

tMap is used to autojoin the entry with itself (here represented by a tFixedFlowInput).

a tUniqRow is used on the main flow to get only the 1st rw for a given contract_no.

Here is the tMap :

Capture.PNG

On the left side:

- use join model = Left Outer Join in case of only 1 row for a contract_no

- filter on the name to avoid the rows to be join with themself

On the right side:

- in case of null value, replace by an empty string ("")

 

Hope this helps.


TRF
5 REPLIES
Nine Stars TRF
Nine Stars

Re: Data arrangement: multiple rows data in one row

Here is what you expect.

Capture.PNG

tMap is used to autojoin the entry with itself (here represented by a tFixedFlowInput).

a tUniqRow is used on the main flow to get only the 1st rw for a given contract_no.

Here is the tMap :

Capture.PNG

On the left side:

- use join model = Left Outer Join in case of only 1 row for a contract_no

- filter on the name to avoid the rows to be join with themself

On the right side:

- in case of null value, replace by an empty string ("")

 

Hope this helps.


TRF
Six Stars

Re: Data arrangement: multiple rows data in one row

Hi,
Thanks for solution.
I want to link delimited file as input to tUniqRow
but getting a warning on the tInputDelimited as This component shud have input links

 

Six Stars

Re: Data arrangement: multiple rows data in one row

Sorry i entered wrong component as input component..my mistake
Five Stars

Re: Data arrangement: multiple rows data in one row

Hi ,

I want to add another approach of getting a solution for this problem 

 

2017-07-05 11_42_31-Talend Data Fabric (6.3.1.20161216_1026) _ BigDataDemo (Connection_ Local).png2017-07-05 11_42_58-Talend Data Fabric (6.3.1.20161216_1026) _ BigDataDemo (Connection_ Local).png2017-07-05 11_43_09-Talend Data Fabric (6.3.1.20161216_1026) _ BigDataDemo (Connection_ Local).png2017-07-05 11_43_22-Talend Data Fabric (6.3.1.20161216_1026) _ BigDataDemo (Connection_ Local).png

Moderator

Re: Data arrangement: multiple rows data in one row

Hello,

Thanks for posting your another approach on forum.

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.