topic Re: Integrating Seperate Columns in to one Column in Talend Studio

Integrating Seperate Columns in to one Column

_AnonymousUser — Sat, 16 Nov 2024 13:28:09 GMT

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

Re: Integrating Seperate Columns in to one Column

Anonymous — Fri, 09 Apr 2010 07:07:19 GMT

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

Re: Integrating Seperate Columns in to one Column

Anonymous — Fri, 09 Apr 2010 07:31:08 GMT

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

Re: Integrating Seperate Columns in to one Column

Anonymous — Fri, 09 Apr 2010 09:23:31 GMT

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

Re: Integrating Seperate Columns in to one Column

Anonymous — Fri, 09 Apr 2010 09:42:53 GMT

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

Re: Integrating Seperate Columns in to one Column

Anonymous — Fri, 09 Apr 2010 09:52:10 GMT

Hello Shong
Thanks a lot, for your reply
Merci beacoup
Sumit

Re: Integrating Seperate Columns in to one Column

Anonymous — Mon, 12 Apr 2010 07:02:01 GMT

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

Re: Integrating Seperate Columns in to one Column

Anonymous — Mon, 12 Apr 2010 16:25:39 GMT

:rolleyes:

Re: Integrating Seperate Columns in to one Column

Anonymous — Thu, 15 Apr 2010 08:30:02 GMT

Any Body please help me with the problem.

Re: Integrating Seperate Columns in to one Column

Anonymous — Fri, 16 Apr 2010 06:43:05 GMT

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

Re: Integrating Seperate Columns in to one Column

Anonymous — Mon, 19 Apr 2010 07:41:25 GMT

Thanks a ton shong

Re: Integrating Seperate Columns in to one Column

alevy — Sun, 12 Jun 2011 03:36:27 GMT

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