topic Two for loops possible? in QlikView

Two for loops possible?

chriys1337 — Tue, 13 Feb 2018 10:24:01 GMT

Hi,

I am using a for loop to calculate my Calendarweeks from 2014 to 2019, with usually 52 weeks in a year, except in 2015: 53.

Additionally the Counter is giving each week a running Number till end of 2019 (e.g. 1 to 313).

I am now wondering if it is possible to make it more flexible and maybe use two for loops in one and also take the year 2015 in consideration with its 53 weeks.

Does anybody have an idea? Thx a lot.

//2014
FOR i = 1 to 52;
test:
Load '2014'& num($(i),'00') as YrWk,
RowNo() as Counter

AutoGenerate 1;
next i

//2015
For y=1 to 53;
//test2:
Load '2015' & num($(y),'00') as YrWk, // RowNo()
RowNo() as Counter

AutoGenerate 1;
next y

//2016
For j=1 to 52;
//test2:
Load '2016' & num($(j),'00') as YrWk, // RowNo()
RowNo() as Counter

AutoGenerate 1;
next j

//2017
For k=1 to 52;
//test2:
Load '2017' & num($(k),'00') as YrWk, // RowNo()
RowNo() as Counter

AutoGenerate 1;
next k

//2018
For l=1 to 52;
//test2:
Load '2018' & num($(l),'00') as YrWk, // RowNo()
RowNo() as Counter

AutoGenerate 1;
next l

//2019
For m=1 to 52;
//test2:
Load '2019' & num($(m),'00') as YrWk, // RowNo()
RowNo() as Counter

AutoGenerate 1;
next m

//2020
For n=1 to 52;
//test2:
Load '2020' & num($(n),'00') as YrWk, // RowNo()
RowNo() as Counter

AutoGenerate 1;
next n

Re: Two for loops possible?

Peter_Cammaert — Tue, 13 Feb 2018 10:37:19 GMT

Yes, you can easily nest FOR loops. Off the top of my head:

FOR y = 2014 TO 2020

LET wend = 52;

IF (y = 2015) THEN

LET wend = 53;

END IF

FOR w = 1 TO wend

CalendarTable:

LOAD RowNo() AS Counter,

'$(y)' & num(w, '00') AS YrWk // Non-numerical

AUTOGENERATE 1;

NEXT w

NEXT y

That's what you asked for. An alternative that is performing much better is this:

FOR y = 2014 TO 2020

LET wend = 52;

IF (y = 2015) THEN

LET wend = 53;

END IF

CalendarTable:

LOAD RowNo() AS Counter,

'$(y)' & num(IterNo(), '00') AS YrWk // Non-numerical

AUTOGENERATE 1

WHILE IterNo() <= $(wend);

NEXT y

To make this work better in a QlikView sense , you could store the RowNo() and string representation as a Dual() value in the same field.

Best,

Peter

[Edit] corrected a few spelling mistakes.

Re: Two for loops possible?

ali_hijazi — Tue, 13 Feb 2018 10:44:32 GMT

why are you complicating things

you can create a master calendar (check attached)

and then you can use inside it:

Week(date) = number week

weekname(date) = year & week

Re: Two for loops possible?

chriys1337 — Tue, 13 Feb 2018 11:46:59 GMT

Thank you very much!