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

[Anonymous]

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

[Anonymous]

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.