topic Pivot and aggregate rows in Talend Studio

Pivot and aggregate rows

jcpreaux — Thu, 25 Jan 2018 17:34:50 GMT

Hello,

I've 3 tMysqlInput components in a job which return these datas :

n°1 :

CustomerID|Pack_1

1|Yes

2|Yes

n°2 :

CustomerID|Pack_2

1|Yes

3|Yes

n°3 :

CustomerID|Pack_3

1|Yes

4|Yes

And i'm trying to get this kind of result :

CustomerID|Pack_1|Pack_2|Pack_3

1|Yes|Yes|Yes

2|Yes||

3||Yes|

4|||Yes

I tried whith tMap component whitout success, any idea ?

Thank you

JCP

Re: Pivot and aggregate rows

Anonymous — Fri, 26 Jan 2018 10:26:05 GMT

Hi jcpreaux

First, use a tunite to merge the three input data, and then use a tDenormalize to denormalize the second column with a delimiter, finally, extract data from the second coumn to multiple fields with tExtractDelimitedFields, the job looks like:

tMysqlInput1----tUnite--tDenormalize--->tExtractDelimitedFields-->tLogRow

tMysqlInput2----

tMysqlInput3----

Let me know if it works!

Regards

Shong