topic Loop through Dates in QlikView

Loop through Dates

Sun, 27 Dec 2015 11:01:11 GMT

Hi Experts,

I have data for a period of time from which i have taken out minimum and maximum date.

Now i have a list of processes. I want to create a dummy data by looping the dates.

Say minimum date is 12 Dec 2015 and Maximum Date is 15 Dec 2015.

and consider processes : process1, process2 and process3.

Then I want to create data in qlikview though script as :

Row	Process	Date	Some Extra Field
Row1	Process1	12 Dec 2015	Null
Row2	Process2	12 Dec 2015	Null
Row3	Process3	12 Dec 2015	Null
Row4	Process1	13 Dec 2015	Null
Row5	Process2	13 Dec 2015	Null
Row6	Process3	13 Dec 2015	Null
Row7	Process1	14 Dec 2015	Null
Row8	Process2	14 Dec 2015	Null
Row9	Process3	14 Dec 2015	Null
Row10	Process1	15 Dec 2015	Null
Row11	Process2	15 Dec 2015	Null
Row12	Process3	15 Dec 2015	Null

How to this?

Can anyone help?

Regards,

Anjali Gupta

Re: Loop through Dates

Kushal_Chawda — Sun, 27 Dec 2015 11:27:38 GMT

Data:

LOAD Process,date(date#(MaxDate,'DD MMM YYYY'),'DD MMM YYYY') as MAX_DATE,

date(date#(MinDate,'DD MMM YYYY'),'DD MMM YYYY') as MIN_DATE Inline [

Process, MaxDate, MinDate

Process1, 15 Dec 2015, 12 Dec 2015

Process2, 15 Dec 2015, 12 Dec 2015

Process3, 15 Dec 2015, 12 Dec 2015 ];

Final:

NoConcatenate

LOAD Process,

'Row '&RowNo() as Row,

date(MIN_DATE+IterNo()-1,'DD MMM YYYY') as Date

Resident Data

While MIN_DATE+IterNo()-1<=MAX_DATE;

DROP Table Data;

Re: Loop through Dates

Sun, 27 Dec 2015 11:43:19 GMT

Hi Kush,

Thanks for the quick response

But the minimum date, maximum date and processes provided by me are static.

While the list of processes and date will be dynamic and change as per the data.

Regards,

Anjali Gupta

Re: Loop through Dates

Kushal_Chawda — Sun, 27 Dec 2015 11:52:26 GMT

So your first step would be to calculate the Min & Max Date

Data:

Load Process

min(Date) as MIN_DATE

max(Date) as MAX_DATE

From Table

group by Process;

Final:

NoConcatenate

LOAD Process,

'Row '&RowNo() as Row,

date(MIN_DATE+IterNo()-1,'DD MMM YYYY') as Date

Resident Data

While MIN_DATE+IterNo()-1<=MAX_DATE;

DROP Table Data;

Re: Loop through Dates

Sun, 27 Dec 2015 13:01:42 GMT

Hi Kush,

Thanks a lot for the help.

I have one more query related to this.

As mentioned I have a table with the fields: Process, Date, [Value]

Which looks like

Process	Date	Value
Process1	12 Dec 2015	Not required
Process2	13 Dec 2015	Not required
Process3	12 Dec 2015	Not required
Process1	13 Dec 2015	Not required
Process2	14 Dec 2015	Not required
Process3	13 Dec 2015	Not required
Process1	14 Dec 2015	Not required
Process2	15 Dec 2015	Not required
Process3	15 Dec 2015	Not required

And after using the below script :

Data:

Load Process

min(Date) as MIN_DATE

max(Date) as MAX_DATE

From Table

group by Process;

I get :

Process	MIN_DATE	MAX_DATE
Process1	12 Dec 2015	14 Dec 2015
Process2	13 Dec 2015	15 Dec 2015
Process3	12 Dec 2015	15 Dec 2015

whereas I want

Process	MIN_DATE	MAX_DATE
Process1	12 Dec 2015	15 Dec 2015
Process2	12 Dec 2015	15 Dec 2015
Process3	12 Dec 2015	15 Dec 2015

Please help me with your valuable knowledge.

Regards,

Anjali Gupta