Integrating Seperate Columns in to one Column

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
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.