Column transformation -and finding distinct and corresponding values

Five Stars

Column transformation -and finding distinct and corresponding values

Hi,

Can anyone please help me over this.

My input in excel is like below:

Source_Field Source_value Target_value
Field1 Src1 Tr1
Field2 Src2 Tr2
Field1 Src3 Tr3
Field1 Src4 Tr4
Field3 Src5 Tr5
Field2 Src6 Tr6

 

And I want output like this:

Field1   Field2   Field3  
Source_value Target_value Source_value Target_value Source_value Target_value
Src1 Tr1 Src2 Tr2 Src5 Tr5
Src3 Tr3 Src6 Tr6    
Src4 Tr4        

 

How can I get this using talend?

Thanks

Fifteen Stars TRF
Fifteen Stars

Re: Column transformation -and finding distinct and corresponding values

here is my proposal:

Capture.PNG

1st step add a sequence number for each value of source_field and memorize the result in a tHashOutput:

Capture.PNG

 

2nd step, join different source_field values with the same rank and format the output flow.

Here is the join to get field1 and associated field2, just repeat the same operation for field3:

Capture.PNG

And here is the right part to format the output flow:

Capture.PNG

And the final result:

Starting job test at 00:04 05/07/2017.

[statistics] connecting to socket on port 3583
[statistics] connected
.-------------+-------------+-------------+-------------+-------------+-------------.
|                                    tLogRow_48                                     |
|=------------+-------------+-------------+-------------+-------------+------------=|
|field1_source|field1_target|field2_source|field2_target|field3_source|field3_target|
|=------------+-------------+-------------+-------------+-------------+------------=|
|src1         |tr1          |src2         |tr2          |src5         |tr5          |
|src3         |tr3          |src6         |tr6          |             |             |
|src4         |tr4          |             |             |             |             |
'-------------+-------------+-------------+-------------+-------------+-------------'
[statistics] disconnected
Job test ended at 00:04 05/07/2017. [exit code=0]

Hope this helps.


TRF

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch