For each & For - looping throught data

[Anonymous]

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?

[dineshraj]

Hi gsmajdor

Use this Script

Load

Employee,

Department,

Min(WorkingDate) as StartDate,

Max(WokingDate) as EndDate,

From........

Group by Employee,Department;

Thanks

[Anonymous]

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.

[Anonymous · Accepted solution]

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;

[Anonymous]

Thanks!

It works