One Star

Integrating Seperate Columns in to one Column

Hi
I have a csv file which have follwong fields.
timestamp;var1_min;var1_max;var1_avg;var2_min;var2_max;var2_avg;var3_min;var3_max;var3_avg
and I would like to create a table fromt this file as follows
Timestamp Variable Min Max Average
####### 1 - - -
####### 2 - - -
####### 3 - - -

So the question is how i can integrate the min max and average values of different variables in to one column with respect to column variable and timestamp.
Thank You in advance
11 REPLIES
One Star

Re: Integrating Seperate Columns in to one Column

I am still struggling with the problem stated above. I am using tmap but I dont see how can I 'break one row into multiple rows'(or multiple columns into one column) . Any help in this regard would be of great help.
Thank You in advance
Community Manager

Re: Integrating Seperate Columns in to one Column

Hello
Here is a demo job, for exampe
in.csv:

12-04-2010;v1min;v1max;v1avg;v2min;v2max;v2avg;v3min;v3max;v3avg
13-04-2010;v1min;v1max;v1avg;v2min;v2max;v2avg;v3min;v3max;v3avg

code on tJavaRow:
//Code generated according to input schema and output schema
output_row.timestamp = input_row.timestamp;
output_row.conn = input_row.var1_min+","+input_row.var1_max+","+input_row.var1_avg+";"+input_row.var2_min+","+input_row.var2_max+","+input_row.var2_avg+";"+input_row.var3_min+","+input_row.var3_max+","+input_row.var3_avg;

Result:
Starting job forum10531 at 14:26 09/04/2010.
connecting to socket on port 3442
connected
.----------+-------+-----+-----+-----.
| tLogRow_1 |
|=---------+-------+-----+-----+----=|
|timestamp |varible|min |max |avg |
|=---------+-------+-----+-----+----=|
|12-04-2010|1 |v1min|v1max|v1avg|
|12-04-2010|2 |v2min|v2max|v2avg|
|12-04-2010|3 |v3min|v3max|v3avg|
|13-04-2010|1 |v1min|v1max|v1avg|
|13-04-2010|2 |v2min|v2max|v2avg|
|13-04-2010|3 |v3min|v3max|v3avg|
'----------+-------+-----+-----+-----'
disconnected
Job forum10531 ended at 14:26 09/04/2010.

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Integrating Seperate Columns in to one Column

Thanks a ton for this solution. It work wonders when var1min var1max ...... fields are strings.
But in my case these are float values. I have tried your proposed solution for float values but it is not working. Smiley Sad
Example of the input csv file
Timestamp;var1min;var1max;var1avg;var2min;var2max;var2avg;var3min;var3max;var3avg
06/04/2010 23:50;384.74;553.46;540.27;42.08;-11.28;-5.74;-6.26;0.91;384.85
06/04/2010 23:40;362.97;457.45;450.44;22.15;-12.37;-7.62;-8.80;1.02;363.12
06/04/2010 23:30;303.42;376.64;359.50;20.58;-13.21;-6.32;-10.81;1.29;303.61
06/04/2010 23:20;360.50;462.34;378.88;28.95;-11.71;-6.92;-9.11;0.69;360.64
06/04/2010 23:10;298.07;428.64;402.27;35.52;-17.19;-8.62;-10.12;1.80;298.46
06/04/2010 23:00;248.69;321.46;314.79;18.31;-16.35;-9.91;-13.55;1.21;249.01

Expected Output
Timestamp Variable Min Max Avg
06/04/2010 23:50 1 553.46 540.27 42.08
06/04/2010 23:40 1 457.45 450.44 22.15
06/04/2010 23:30 1 376.64 359.50 20.58
06/04/2010 23:20 1 462.34 378.88 28.95
06/04/2010 23:10 1 428.64 402.27 35.52
06/04/2010 23:00 1 321.46 314.79 18.31
06/04/2010 23:50 2 42.08 -11.28 -5.74
06/04/2010 23:40 2 22.15 -12.37 -7.62
06/04/2010 23:30 2 20.58 -13.21 -6.32
06/04/2010 23:20 2 28.95 -11.71 -6.92
06/04/2010 23:10 2 35.52 -17.19 -8.62
06/04/2010 23:00 2 18.31 -16.35 -9.91
06/04/2010 23:50 3 -6.26 0.91 384.85
06/04/2010 23:40 3 -8.80 1.02 363.12
06/04/2010 23:30 3 -10.81 1.29 303.61
06/04/2010 23:20 3 -9.11 0.69 360.64
06/04/2010 23:10 3 -10.12 1.80 298.46
06/04/2010 23:00 3 -13.55 1.21 249.01

I am sorry I should have posted the sample beforehand. Please help me in this regard.
Thanks in advance
Community Manager

Re: Integrating Seperate Columns in to one Column

Hello
The only difference is that the variable value bases on different timestamp. I will re-design the job a little and show you...
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Integrating Seperate Columns in to one Column

Hello Shong
Thanks a lot, for your reply
Merci beacoup Smiley Happy
Sumit
One Star

Re: Integrating Seperate Columns in to one Column

Hello Shong
I request if you can upload the redesigned job since I am still struggling with the same problem.
Thank you in advance
Sumit
One Star

Re: Integrating Seperate Columns in to one Column

:rolleyes:
One Star

Re: Integrating Seperate Columns in to one Column

Any Body please help me with the problem.
Community Manager

Re: Integrating Seperate Columns in to one Column

Hello
I have tried your proposed solution for float values but it is not working

I redesign the job a little to adjust to your request more close.
variable.csv:

1;var1
2;var2
3;var3

tLogRow_1:
.----------------+-------+------+------+------.
| tLogRow_1 |
|=---------------+-------+------+------+-----=|
|timestamp |varible|min |max |avg |
|=---------------+-------+------+------+-----=|
|06/04/2010 23:50|var1 |384.74|553.46|540.27|
|06/04/2010 23:40|var1 |362.97|457.45|450.44|
|06/04/2010 23:30|var1 |303.42|376.64|359.5 |
|06/04/2010 23:20|var1 |360.5 |462.34|378.88|
|06/04/2010 23:10|var1 |298.07|428.64|402.27|
|06/04/2010 23:00|var1 |248.69|321.46|314.79|
|06/04/2010 23:50|var2 |42.08 |-11.28|-5.74 |
|06/04/2010 23:40|var2 |22.15 |-12.37|-7.62 |
|06/04/2010 23:30|var2 |20.58 |-13.21|-6.32 |
|06/04/2010 23:20|var2 |28.95 |-11.71|-6.92 |
|06/04/2010 23:10|var2 |35.52 |-17.19|-8.62 |
|06/04/2010 23:00|var2 |18.31 |-16.35|-9.91 |
|06/04/2010 23:50|var3 |-6.26 |0.91 |384.85|
|06/04/2010 23:40|var3 |-8.8 |1.02 |363.12|
|06/04/2010 23:30|var3 |-10.81|1.29 |303.61|
|06/04/2010 23:20|var3 |-9.11 |0.69 |360.64|
|06/04/2010 23:10|var3 |-10.12|1.8 |298.46|
|06/04/2010 23:00|var3 |-13.55|1.21 |249.01|
'----------------+-------+------+------+------'

tLogRow_2
.----------------+-------+------+------+------.
| tLogRow_2 |
|=---------------+-------+------+------+-----=|
|timestamp |varible|min |max |avg |
|=---------------+-------+------+------+-----=|
|06/04/2010 23:50|1 |384.74|553.46|540.27|
|06/04/2010 23:40|1 |362.97|457.45|450.44|
|06/04/2010 23:30|1 |303.42|376.64|359.5 |
|06/04/2010 23:20|1 |360.5 |462.34|378.88|
|06/04/2010 23:10|1 |298.07|428.64|402.27|
|06/04/2010 23:00|1 |248.69|321.46|314.79|
|06/04/2010 23:50|2 |42.08 |-11.28|-5.74 |
|06/04/2010 23:40|2 |22.15 |-12.37|-7.62 |
|06/04/2010 23:30|2 |20.58 |-13.21|-6.32 |
|06/04/2010 23:20|2 |28.95 |-11.71|-6.92 |
|06/04/2010 23:10|2 |35.52 |-17.19|-8.62 |
|06/04/2010 23:00|2 |18.31 |-16.35|-9.91 |
|06/04/2010 23:50|3 |-6.26 |0.91 |384.85|
|06/04/2010 23:40|3 |-8.8 |1.02 |363.12|
|06/04/2010 23:30|3 |-10.81|1.29 |303.61|
|06/04/2010 23:20|3 |-9.11 |0.69 |360.64|
|06/04/2010 23:10|3 |-10.12|1.8 |298.46|
|06/04/2010 23:00|3 |-13.55|1.21 |249.01|
'----------------+-------+------+------+------'

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Integrating Seperate Columns in to one Column

Thanks a ton shong
Smiley Happy
Seven Stars

Re: Integrating Seperate Columns in to one Column

From v4.2.0, the tSplitRow component handles this very well.