topic Re: For Loop in QlikView

For Loop

userid128223 — Tue, 28 Feb 2017 19:06:55 GMT

I need to loop 3 times thru each month. days 01-10, 11-20, 21-31

//Starting Variable to pass YYMM

vStartYYMM='201702'

Loop1

vStartDate=$(vStartYYMM)01

vStartDate=$(vStartYYMM)10

Loop2

vStartDate=$(vStartYYMM)11

vStartDate=$(vStartYYMM)20

Loop3

vStartDate=$(vStartYYMM)21

vStartDate=$(vStartYYMM)31

How do you create the days portion via loop.

Re: For Loop

maxgro — Tue, 28 Feb 2017 19:25:42 GMT

Why do you need 3 loop?

LET vStartYYMM='201702';

FOR i=1 to 10

LET vStartDate=$(vStartYYMM)& num($(i), '00');

TRACE $(vStartDate);

NEXT

FOR i=1 to 10

LET vStartDate=$(vStartYYMM)& num(10 + $(i), '00');

TRACE $(vStartDate);

NEXT

FOR i=1 to 11

LET vStartDate=$(vStartYYMM)& num(20 + $(i), '00');

TRACE $(vStartDate);

NEXT

Re: For Loop

userid128223 — Tue, 28 Feb 2017 19:50:59 GMT

Because i am doing data pull by increments.

for u = 1 to 31 step 11

Let vStartu= $(vmonth0)&num($(u),'00');

Let vEndu= $(vmonth0)&num(9+$(u),'00');

trace 'vStartu = '$(vStartu);

trace 'vEndu = ' $(vEndu);

so above works to a certain degree but not exactly right.

OUTPUT

'vStartu = '20170201

'vEndu = ' 20170211

'vStartu = '20170212

'vEndu = ' 20170222

'vStartu = '20170223

'vEndu = ' 20170233

Last vEndu date is exceeding 31 days.

Re: For Loop

aarkay29 — Tue, 28 Feb 2017 21:35:39 GMT

Will this work for You??

LET vStart=NUM(MonthStart(TODAY()));

TEMP:

LOAD

$(vStart)+ROWNO()-1 AS NumDate,

Date($(vStart)+ROWNO()-1,'DDMMYYYY') as Date

AutoGenerate

Num#(Date(MonthEnd(TODAY()),'DD'));

DateLoop:

Load Distinct

If(Left(Date,2)<='10',Makedate(Year(Date),Month(Date),10),

If(Left(Date,2)<='20' and Left(Date,2)>'10',Makedate(Year(Date),Month(Date),20),

If( Left(Date,2)>'20',Makedate(Year(Date),Month(Date),num#(Date(MonthEnd(Date),'DD')))

)

) as EndDate,

If(Left(Date,2)<='10',Makedate(Year(Date),Month(Date),1),

If(Left(Date,2)<='20' and Left(Date,2)>'10',Makedate(Year(Date),Month(Date),11),

If( Left(Date,2)>'20',Makedate(Year(Date),Month(Date),21)

)

) as StartDate

Resident TEMP;

Drop table TEMP;

Re: For Loop

maxgro — Wed, 01 Mar 2017 07:51:17 GMT

LET vMonthStart = MonthStart(Date#('$(vStartYYMM)', 'YYYYMM'));

LET vMonthEnd = MonthEnd(Date#('$(vStartYYMM)', 'YYYYMM'));

TRACE $(vMonthStart);

FOR i=0 TO 2

LET vStartDate = Date('$(vMonthStart)' + 10 * i, 'YYYYMMDD');

TRACE $(vStartDate);

IF (i=2) THEN

LET vEndDate = Date('$(vMonthEnd)', 'YYYYMMDD');

ELSE

LET vEndDate = Date('$(vMonthStart)' + 10 * i + 9, 'YYYYMMDD');

ENDIF

TRACE $(vEndDate);

NEXT

Re: For Loop

userid128223 — Wed, 01 Mar 2017 16:33:05 GMT

Thank you.

Re: For Loop

userid128223 — Wed, 01 Mar 2017 18:18:12 GMT

so if you add 10 days to a date and then multiple by 0, you get the same date you started with?. It won't result in NULL?

2017/02/01 + 10 = 2017/02/11 * 0 = 2017/02/01 ?

Re: For Loop

maxgro — Wed, 01 Mar 2017 19:16:10 GMT

multiply is before sum

2017/02/01 + 10 * 0 = 2017/02/01 + 0 = 2017/02/01

2017/02/01 + 10 * 1 = 2017/02/01 + 10 = 2017/02/11

Re: For Loop

userid128223 — Thu, 02 Mar 2017 01:30:15 GMT

Thank you.