Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to loop through data like this:
Employee, StartDate, EndDate, Department
Mark, 01/01/2017, 02/01/2017, HR
Joey, 01/01/2017, 03/01/2017, HR
Ana, 02/01/2017, 06/01/2017, BI
//DD/MM/YYYY
and then load a table which should look like:
Employee, WorkingDate, Department
Mark, 01/01/2017, HR
Mark, 02/01/2017, HR
Joey, 01/01/2017, HR
Joey, 02/01/2017, HR
Joey, 03/01/2017, HR
Ana, 02/01/2017, BI
Ana, 03/01/2017, BI
Ana, 04/01/2017, BI
Ana, 05/01/2017, BI
Ana, 06/01/2017, BI
The purpose is to get a table with all working dates for each employee (from StartDate to EndDate).
I guess I should use For each (employee) & For (from StartDate to EndDate)... but don't know how.
Any suggestions?
You can use while function like this:
Data:
LOAD
Employee,
Department,
date(StartDate+IterNo()-1) as Workingdate
Resident Temp
while EndDate >= StartDate+IterNo()-1;
drop tables Temp;
Hi gsmajdor
Use this Script
Load
Employee,
Department,
Min(WorkingDate) as StartDate,
Max(WokingDate) as EndDate,
From........
Group by Employee,Department;
Thanks
Hi dineshraj,
I think you did it in reverse order.
This is my input:
Employee, StartDate, EndDate, Department
And the output that I am trying to get is:
Employee, WorkingDate, Department
So I cannot load min(WorkingDate), as the WorkingDate does not exists.
You can use while function like this:
Data:
LOAD
Employee,
Department,
date(StartDate+IterNo()-1) as Workingdate
Resident Temp
while EndDate >= StartDate+IterNo()-1;
drop tables Temp;
Thanks!
It works