tUnpivotROW - How to set 1st line in csv file as ROW KEY

Anonymous — Sat, 28 Nov 2015 15:13:15 GMT

Hi,
From a csv file built like that :

EmptyRow;EmptyRow;Param1;Param2
Date1; ;Hour1;ValParam1;ValParam2
Date2;;Hour2;ValParam3;ValParam4

this csv file has a generic schema :
Column;Column1;Column2;Column3;Column4

I used tUnpivotRow component with Row keys (Problem : i can't declare the first CSV Line as Row Key):
Column (in fact Date Column)
Column1 (in fact Hour Column)

i obtain the result bellow :

Date1; Hour1;Param1
Date1; Hour1;Param2
Date1; Hour1;ValParam1
Date1; Hour1;ValParam2
Date1; Hour1;ValParam3
Date1; Hour1;ValParam4
Date2;Hour2;Param1
Date2;Hour2;Param2
Date2; Hour2;ValParam1
Date2; Hour2;ValParam2
Date2; Hour2;ValParam3
Date2; Hour2;ValParam4

But i want this result :

Date1; Hour1;Param1;ValParam1
Date1; Hour1;Param2;ValParam2
Date2; Hour2;Param1;ValParam3
Date2;Hour2;Param2;ValParam4

Question : How to set tUnpivotRow component to INNER JOIN Columns declared in RowKey tUnpivotRow settings AND each row from the csv 1rst line ?

Thanks for reply.
Alain

Re: tUnpivotROW - How to set 1st line in csv file as ROW KEY

Anonymous — Wed, 02 Dec 2015 03:53:48 GMT

Hi
concatenate Date column and Hour column to be one column on a tJavaRow, this column will be the pivot key on tUnpivotRow, after transforming the data, extract fields from the column with tExtractDelimitedFields, the job design looks like:
tFileInputDelimited--main--tJavaRow--main--tUnpivotRow--main--tExtractDelimitedField--main--tLogRow

on tJavaRow; concatenate Date column and Hour column to be one column, eg:
output_row.columnKey=input_row.Date+"_"+input_row.Hour;
output_row.other column=input_row.other column;

the output column columnKey will be the rows key on tUnpivotRow.

on tExtractDelimited: extract Date column and Hour column from columnKey column.

topic tUnpivotROW - How to set 1st line in csv file as ROW KEY in Talend Studio

tUnpivotROW - How to set 1st line in csv file as ROW KEY

Re: tUnpivotROW - How to set 1st line in csv file as ROW KEY