Group By

[poortman]

Hi all,

I've got a problem with a group by-statement in my script. I want to know the advancement from employees to a cost center. The source-table is as follows:

Worker	Cost center	Startdate	Enddate
00017505	51102	10-10-2013	31-03-2014
00017505	51102	01-04-2014	31-12-2014
00017505	51102	01-01-2015	30-04-2015
00017505	51102	01-05-2015	30-04-2016
00017505	51137	01-05-2016	30-06-2020
00017505	51102	01-07-2020	31-12-2020
00017505	51102	01-01-2021	-

TMP:
Load distinct
Medewerker_code                                                                             As Worker,
Kostenplaats                                                                                        As Cost_center,
Date(Date#(Begindatum_functie),'DD-MM-YYYY')                As Startdate,
Date(Date#(Einddatum_functie),'DD-MM-YYYY')                   As Enddate
From [lib://qs_data/HR/HR-app/QVD/PURPLE_AFAS_FUNCTIE_FORMATIEVERDELING.qvd] (qvd)
Where Medewerker_code = '00017505';

I want the output to be like the following table, 

where the field [Days between ]is calculated when the cost center changes, enddate former cost center minus startdate current cost center.

Worker	Cost center	Startdate	Enddate	Former Cost center	Days between
00017505	51102	10-10-2013	30-04-2016	-	1
00017505	51137	01-05-2016	30-06-2020	51102	1
00017505	51102	01-07-2020	-	51137	1

[Taoufiq_Zarra · Accepted solution]

@poortman  in Script you can use :

Input:
LOAD * INLINE [
    Worker, Cost center, Startdate, Enddate
    00017505, 51102, 10-10-2013, 31-03-2014
    00017505, 51102, 01-04-2014, 31-12-2014
    00017505, 51102, 01-01-2015, 30-04-2015
    00017505, 51102, 01-05-2015, 30-04-2016
    00017505, 51137, 01-05-2016, 30-06-2020
    00017505, 51102, 01-07-2020, 31-12-2020
    00017505, 51102, 01-01-2021, 
];


Tmp:
noconcatenate

load *,rowno() as rowtmp,if(rowno()=1,1,if(peek([Cost center])=[Cost center],peek(IDtmp),peek(IDtmp)+1)) as IDtmp,
if(peek([Cost center])=[Cost center],peek([Former Cost center]),peek([Cost center])) as [Former Cost center],
if(peek([Cost center])<>[Cost center],peek(Enddate),peek(Enddatetmp)) as Enddatetmp
 resident Input;

drop table Input;


output:
noconcatenate
load *,Startdate-Enddatetmp as [Days between];
load Worker,[Cost center],Date(FirstSortedValue(Startdate,rowtmp)) as Startdate, Date(FirstSortedValue(Enddate,-rowtmp)) as Enddate,[Former Cost center],Enddatetmp resident Tmp group by Worker,IDtmp,[Cost center],[Former Cost center],Enddatetmp;

drop table Tmp;
drop fields Enddatetmp;

output:

[poortman]

Thanks Taoufiq, seems to work! 

[poortman]

@Taoufiq_Zarra Hi Taoufiq (and others), 

Still have a problem when the input is like this:

Input:
LOAD * INLINE [
Worker, Cost center, Startdate, Enddate
00018847, 51104, 01-01-2017, 31-12-2017
00018847, 51104, 01-01-2018, 31-12-2018
00018847, 51104, 01-01-2019, 31-10-2019
00018847, 51104, 01-11-2019, 31-12-2019
00018847, 51102, 01-01-2020, 31-12-2020
00018847, 51104, 01-01-2020, 31-12-2020
00018847, 51102, 01-01-2021, 31-05-2021
00018847, 51104, 01-01-2021, 31-05-2021
00018847, 51102, 01-06-2021,
00018847, 51104, 01-06-2021,
00018847, 51120, 01-06-2021,
];

The output is like this:

Worker	Cost center	Startdate	Enddate	Former Cost center	Days between
00018847	51104	01-01-2017	31-12-2019	-	-
00018847	51102	01-01-2020	31-12-2020	51104	1,00000000000000
00018847	51104	01-01-2020	31-12-2020	51102	-365,00000000000000
00018847	51102	01-01-2021	31-05-2021	51104	1,00000000000000
00018847	51104	01-01-2021	31-05-2021	51102	-150,00000000000000
00018847	51102	01-06-2021	-	51104	1,00000000000000
00018847	51104	01-06-2021	-	51102	-
00018847	51120	01-06-2021	-	51104	-

But I want it to be like this:

Worker	Cost center	Startdate	Enddate	Former Cost center	Days between
00018847	51104	01-01-2017	31-12-2019	-
00018847	51102	01-01-2020	31-12-2020	51104	1,00000000000000
00018847	51104	01-01-2020	31-12-2020
00018847	51102	01-01-2021	31-05-2021
00018847	51104	01-01-2021	31-05-2021
00018847	51102	01-06-2021	-
00018847	51104	01-06-2021	-
00018847	51108	01-06-2021	-	51104	1,00000000000000

Can you help me get this right?

Thanks in advance,

Niek Poortman