Talend community forum / separate between date and time to filter data

Gooner · 2012-06-11 14:40:23

Hi,

I want to recover my database, the first date and last date of the score every day.

I've used tAggregateRow, with Group by "user id", but it only shows me the first day and the first date for each user.

this is the result

I added group by "checktime" he displays all dates of the day, but i need only the first "date" of every day

this is the result

what should i do please?

shong · 2012-06-11 15:18:26

Hi
Your images do not display!
About your request, you need to compute the interval time between first date and end date, and then use a tLoop to output each row. Can you please give us an example input data and what's your expected data?

Best regards
Shong

Gooner · 2012-06-11 16:38:38

Hi,

this is a little example...

input table:

user_id || date

1 || 2012-06-05 07:30:55
1 || 2012-06-05 10:40:00
1 || 2012-06-05 15:15:29
1 || 2012-06-05 18:33:09

2 || 2012-06-05 09:09:09
2 || 2012-06-05 17:17:17
2 || 2012-06-05 19:19:19

1 || 2012-06-06 08:30:00
1 || 2012-06-06 09:33:33
1 || 2012-06-06 11:30:00
1 || 2012-06-06 14:55:55

2 || 2012-06-06 07:30:55
2 || 2012-06-06 13:13:13

1 || 2012-06-10 08:30:00
1 || 2012-06-10 09:33:33
1 || 2012-06-10 11:30:00

2 || 2012-06-11 07:30:55
2 || 2012-06-11 13:13:13
2 || 2012-06-11 14:55:55

this is what i expect:

user_id || date

1 || 2012-06-05 07:30:55
1 || 2012-06-05 18:33:09

2 || 2012-06-05 09:09:09
2 || 2012-06-05 19:19:19

1 || 2012-06-06 08:30:00
1 || 2012-06-06 14:55:55

2 || 2012-06-06 07:30:55
2 || 2012-06-06 13:13:13

1 || 2012-06-10 08:30:00
1 || 2012-06-10 11:30:00

2 || 2012-06-11 07:30:55
2 || 2012-06-11 14:55:55

shong · 2012-06-12 04:16:09

Hi
Here a scenario showing how to implement your request.
code on tJavaRow_1

Code:

output_row.id = input_row.id;
output_row.date = TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", input_row.firstDate)+";"+TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", input_row.lastDate);

Result printed on the console:

Code:

Starting job topic24418 at 10:12 12/06/2012.


[statistics] connecting to socket on port 3579
[statistics] connected
.--+-------------------.
|      tLogRow_1       |
|=-+------------------=|
|id|date               |
|=-+------------------=|
|2 |2012-06-11 07:30:55|
|2 |2012-06-11 14:55:55|
|2 |2012-06-06 07:30:55|
|2 |2012-06-06 13:13:13|
|1 |2012-06-06 08:30:00|
|1 |2012-06-06 14:55:55|
|2 |2012-06-05 09:09:09|
|2 |2012-06-05 19:19:19|
|1 |2012-06-05 07:30:55|
|1 |2012-06-05 18:33:09|
|1 |2012-06-10 08:30:00|
|1 |2012-06-10 11:30:00|
'--+-------------------'

[statistics] disconnected
Job topic24418 ended at 10:12 12/06/2012. [exit code=0]

Best regards
Shong

Uploaded Images

Gooner · 2012-06-12 15:56:04

Thanks a lot... I'll try your solution

Announcement

Unanswered posts

#1 2012-06-11 14:40:23

separate between date and time to filter data

#2 2012-06-11 15:18:26

Re: separate between date and time to filter data

#3 2012-06-11 16:38:38

Re: separate between date and time to filter data

#4 2012-06-12 04:16:09

Re: separate between date and time to filter data

Code:

Code:

#5 2012-06-12 15:56:04

Re: separate between date and time to filter data

Board footer