From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

Generate 1 row from multiple rows Talend

Highlighted
Five Stars

Generate 1 row from multiple rows Talend

i have fields like this

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

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.

Tags (1)
Highlighted
Community Manager

Re: Generate 1 row from multiple rows Talend

Hi
I think tAggregateRow component can meet your needs, please read the component documentation and learn this component, let me know if you have any questions.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Highlighted
Five Stars

Re: Generate 1 row from multiple rows Talend

Hi Shong,

 

Thanks for your answer. yes i read a little bit about this component, and it almost works.

 

The only thing 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.

 

BR

Reims

Highlighted
Seven Stars

Re: Generate 1 row from multiple rows Talend

Hello,

You can group by name and number,

For the dates, I advise you to use the tAggregateRow component again, this time with the min and max function. so you will have the start and end date of your holiday

Highlighted
Five Stars

Re: Generate 1 row from multiple rows Talend

Hi John, 

 

Thanks for your answer. As said earlier it almost works.

 

The only thing is that it is not considering the consecutive days, so i m tryin to add one condition.

 

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. an then paul had holydays again from 15.09.2019 to 20.09.2019.

 

BR

Reims

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog