Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gsmajdor
Contributor

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?

1 Solution

Accepted Solutions
joonasjaspi
New Contributor III

Re: For each & For - looping throught data

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;

4 Replies
dineshraj
Contributor

Re: For each & For - looping throught data

Hi gsmajdor


Use this Script


Load

Employee,

Department,

Min(WorkingDate) as StartDate,

Max(WokingDate) as EndDate,

From........

Group by Employee,Department;

Thanks

gsmajdor
Contributor

Re: For each & For - looping throught data

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.

joonasjaspi
New Contributor III

Re: For each & For - looping throught data

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;

gsmajdor
Contributor

Re: For each & For - looping throught data

Thanks!

It works

Community Browser