
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For each & For - looping throught data
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?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi gsmajdor
Use this Script
Load
Employee,
Department,
Min(WorkingDate) as StartDate,
Max(WokingDate) as EndDate,
From........
Group by Employee,Department;
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks!
It works
