topic Re: For Next Loop Dates in QlikView

For Next Loop Dates

jeckstein — Thu, 19 Jan 2017 20:08:16 GMT

Hi,

I have a Load statement like the following.

Load

From

Connnectionstartdate2017-01-19enddate2017-01-19

I need to run this same load for all dates between 2015-09-01 and today.

I cannot simply change start date to 2015-09-01 and end date to 2017-01-19. This would give me a undesidered result. What i need to do is make a For next loop for every date between this range. the variable used in this loop would be place in the where 2017-01-19 is in the load script above.

Please let me know of any solutions.

Thanks in advance!!

-Jimmy

Re: For Next Loop Dates

Anonymous — Thu, 19 Jan 2017 20:32:29 GMT

Something like this?

//Assign variables for start date and end date.

let vEndDate=num(today());

let vStartDate=num(date('2015-09-1'));

//Assign variable for number of days between start and end dates.

Let DayCount =$(vEndDate)+1-$(vStartDate);

//Make table of every date between start and end date.

DateFilters:

LOAD

date($(vEndDate)+1-recno(),'YYYY-MM-DD') AS %Dates

AUTOGENERATE $(DayCount);

LET NumRows=FieldValueCount('%Dates');

for i=1 to NumRows

LET vDate = peek('%Dates',$(i)-1,'DateFilters');

load *

from YourTable

where StartDate=$(vDate) and EndDate=$(vDate)

;

Next;

Re: For Next Loop Dates

maxgro — Thu, 19 Jan 2017 20:41:25 GMT

LET a=date#('2015-09-01', 'YYYY-MM-DD');

Do while a < today()

a1 = date(a, 'YYYY-MM-DD');

trace $(a1);

// add your load here using a1 variable

LET a=a+1;

loop