Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
userid128223
Creator
Creator

For Loop

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

1.png

View solution in original post

8 Replies
maxgro
MVP
MVP

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

userid128223
Creator
Creator
Author

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);

next

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.

aarkay29
Specialist
Specialist

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;

maxgro
MVP
MVP

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

1.png

userid128223
Creator
Creator
Author

Thank you.

userid128223
Creator
Creator
Author

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  ?

maxgro
MVP
MVP

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


userid128223
Creator
Creator
Author

Thank you.