Generate 1 row from multiple rows Talend

Question

I ve found on the forum exactly the inverse logic of what i need, so i have to ask :)

i have fields like this

with this kind of sample data

|Marketing|1|Holydays|Henri|2019-10-03T07:00:00.000Z|2019-10-03T15:00:00.000Z|
|Marketing|1|Holydays|Henri|2019-10-04T07:00:00.000Z|2019-10-04T15:00:00.000Z|
|Marketing|1|Holydays|Henri|2019-10-05T07:00:00.000Z|2019-10-05T15:00:00.000Z|
|Marketing|1|Holydays|Henri|2019-10-06T07:00:00.000Z|2019-10-06T15:00:00.000Z|
|Marketing|1|sickness|Henri|2019-10-08T07:00:00.000Z|2019-10-08T15:00:00.000Z|
|Marketing|1|sickness|Henri|2019-10-09T07:00:00.000Z|2019-10-09T15:00:00.000Z|

|IT-Depart|2|Holydays|Paule|2019-11-08T07:00:00.000Z|2019-11-08T15:00:00.000Z|
|IT-Depart|2|Holydays|Paule|2019-11-09T07:00:00.000Z|2019-11-09T15:00:00.000Z|
|IT-Depart|2|Holydays|Paule|2019-11-10T07:00:00.000Z|2019-11-10T15:00:00.000Z|

|Marketing|1|Holydays|Henri|2019-10-17T07:00:00.000Z|2019-10-17T15:00:00.000Z|
|Marketing|1|Holydays|Henri|2019-10-18T07:00:00.000Z|2019-10-18T15:00:00.000Z|

i want has a Result, a single row for absences on consecutive days for the same reason

something like this

|sector|EmployeeNr|Name|Reason|startTime|EndTime|

|Marketing|1|Holydays|Henri|2019-10-03T07:00:00.000Z|2019-10-06T15:00:00.000Z|

|Marketing|1|sickness|Henri|2019-10-08T07:00:00.000Z|2019-10-09T15:00:00.000Z|

|IT-Depart|2|Holydays|Paule|2019-11-08T07:00:00.000Z|2019-11-10T15:00:00.000Z|

|Marketing|1|Holydays|Henri|2019-10-17T07:00:00.000Z|2019-10-18T15:00:00.000Z|

i think that should be something like an aggregate on Date, Reason and EmployeeNr but don t know really how to deal with this.

Any help please?

is your real data sorted like in your example ? – Ibrahim Mezouar Jun 30 '20 at 19:52 — Ibrahim Mezouar, Jun 30 '20 at 19:52
yes Ibrahim , my data are sorted coming from the database – Reims Jul 02 '20 at 08:27 — Reims, Jul 02 '20 at 08:27

score 0 · Answer 1 · answered Jun 29 '20 at 18:04

0

You have to use tAggregateRow component and use below conditions,

min(startTime)
max(EndTime)
group by sector,EmployeeNr,Name,Reason

Below is the snipping,

answered Jun 29 '20 at 18:04

Jim Macaulay

4,709
4
28
53

2

This doesn't take into account the consecutive days for the absences – Ibrahim Mezouar Jun 29 '20 at 21:28
Hi Jim, Thanks for your answer. As Ibrahim said, The only problem is that it is not considering the consecutive days, so i m tryin to add one condition. for example if paul has holydays from 02.06.2019 to 10.06.2019 and then from 15.09.2019 to 20.09.2019 By grouping on EmployeeNr, Reason and applying the min function on the start time and max function on the end time, The result will be like Paul had holydays from 02.06.2019 to 20.09.2019. – Reims Jun 30 '20 at 07:21

score 0 · Answer 2 · answered Jul 01 '20 at 12:14

0

Given that the order is important, you should look into tAggregateSortedRow component : you'll have to sort data on your keys first (sector,#,reason, startdate, then with the component you'll have to group on keys (sector,#,reason, name) and put your operations (min /max date) : as your data is sorted on your input, everytime a change is detected on keys, operations min and max will be executed.

answered Jul 01 '20 at 12:14

Corentin

2,442
1
17
28

Thanks for your answer, but for example if paul has holydays on 02.06.2019, 03.06.2019, 04.06.2019, 05.06.2019, 06.06.2019, 07.06.2019, 08.06.2019, 09.06.2019 10.06.2019 and then on 15.09.2019, 16.09.2019, 17.09.2019, 18.09.2019, 19.09.2019 and 20.09.2019 By grouping on EmployeeNr, Reason and applying the min function on the start time and max function on the end time, The result will be like Paul had holydays from 02.06.2019 to 20.09.2019. Or i want to obtain is Paul had holydays from 02.06.2019 to 10.06.2019. and then again from 15.09.2019 to 20.09.2019. – Reims Jul 02 '20 at 08:31

score 0 · Accepted Answer · answered Jul 02 '20 at 08:41

guys, i found a solution by creating a new column named Sequence, and give this column the same value when this condition is respected ( days are consecutive, and it is related to the same employee, and to the same reason) . I wrote the code in a tjavarow with the Numeric.sequence function and an if (condition)

so for example

 |sector|EmployeeNr|Name|Reason|startTime|EndTime|Sequence

|Marketing|1|Holydays|Henri|2019-10-03T07:00:00.000Z|2019-10-03T15:00:00.000Z|1
|Marketing|1|Holydays|Henri|2019-10-04T07:00:00.000Z|2019-10-04T15:00:00.000Z|1
|Marketing|1|Holydays|Henri|2019-10-05T07:00:00.000Z|2019-10-05T15:00:00.000Z|1
|Marketing|1|Holydays|Henri|2019-10-06T07:00:00.000Z|2019-10-06T15:00:00.000Z|1
|Marketing|1|sickness|Henri|2019-10-08T07:00:00.000Z|2019-10-08T15:00:00.000Z|2
|Marketing|1|sickness|Henri|2019-10-09T07:00:00.000Z|2019-10-09T15:00:00.000Z|2

|IT-Depart|2|Holydays|Paule|2019-11-08T07:00:00.000Z|2019-11-08T15:00:00.000Z|3
|IT-Depart|2|Holydays|Paule|2019-11-09T07:00:00.000Z|2019-11-09T15:00:00.000Z|3
|IT-Depart|2|Holydays|Paule|2019-11-10T07:00:00.000Z|2019-11-10T15:00:00.000Z|3

|Marketing|1|Holydays|Henri|2019-10-17T07:00:00.000Z|2019-10-17T15:00:00.000Z|4
|Marketing|1|Holydays|Henri|2019-10-18T07:00:00.000Z|2019-10-18T15:00:00.000Z|4

and at the end, use the tAggregateRow as suggested by Jim Macaulay

min(startTime)

max(EndTime)

group by EmployeeNr,Name,Reason, Sequence.

And that works!

Thanks all for your help.

Generate 1 row from multiple rows Talend

3 Answers3