topic Re: Problem with do while in QlikView

Problem with do while

Anonymous — Sun, 02 Aug 2015 07:26:32 GMT

Hi All

i have problem with my do while looop .Its not exiting as per requirement ..Here is my code .

My loop has to end on weekend of 30th july 2015 i.e 05-07-2015.

But it is not ending there but loop seems to continuous .

LET vStartDateKey = makedate(2015,06,25);

LET vEndDateKey = MakeDate(2015,06,30);

let vweekstart = num(num#(WeekStart(vStartDateKey)));

let vweekend = num(Num#(WeekStart(vEndDateKey)));

do while weekstart(vStartDateKey)<=Weekend(vEndDateKey)

let vnewweekend =date(date#(vweekstart)+6);

FCT_SALES:

SELECT

DATE_KEY,

SALES_DATE,

STORE_KEY,

DEPT_KEY

WHERE

DATE_KEY BETWEEN '$(vweekstart)' AND '$(vnewweekend)';

let file = Year(date#(vweekstart))&week(date#(vweekstart));

trace file= $(file);

//Store and Drop Table

STORE FCT_DAILY_SALES INTO [./Qvd/$(file).QVD];

DROP TABLE FCT_SALES;

let vweekstart = date(date#(vweekstart)+7);

LOOP;

Re: Problem with do while

sunny_talwar — Sun, 02 Aug 2015 08:15:32 GMT

let vweekstart = date(date#(vweekstart)+7);

LOOP;

I think you are changing vweekstart instead of vStartDateKey

Re: Problem with do while

sunny_talwar — Sun, 02 Aug 2015 08:20:42 GMT

This script seems to be working. You will need to change your script according to your needs

LET vStartDateKey = Num(makedate(2015,06,25));

LET vEndDateKey = Num(MakeDate(2015,06,30));

Do While vStartDateKey <= vEndDateKey

Table:

LOAD $(vStartDateKey) as 1,

$(vEndDateKey) as 2

AutoGenerate 1;

LET vStartDateKey = $(vStartDateKey) + 1;

LOOP

Re: Problem with do while

Anonymous — Sun, 02 Aug 2015 08:22:55 GMT

I thought the same and changed my script to vweekstart and vweekend.BUt still same problem .

LET vStartDateKey = makedate(2015,06,25);

LET vEndDateKey = MakeDate(2015,06,30);

let vweekstart = num(num#(WeekStart(vStartDateKey)));

let vweekend = num(Num#(WeekStart(vEndDateKey)));

do while vweekstart<=vweekend

let vnewweekend =date(date#(vweekstart)+6);

FCT_SALES:

SELECT

DATE_KEY,

SALES_DATE,

STORE_KEY,

DEPT_KEY

WHERE

DATE_KEY BETWEEN '$(vweekstart)' AND '$(vnewweekend)';

let file = Year(date#(vweekstart))&week(date#(vweekstart));

trace file= $(file);

//Store and Drop Table

STORE FCT_DAILY_SALES INTO [./Qvd/$(file).QVD];

DROP TABLE FCT_SALES;

let vweekstart = date(date#(vweekstart)+7);

LOOP;

Re: Problem with do while

Anonymous — Sun, 02 Aug 2015 08:41:16 GMT

Hi i tried but it is exiting .It is working in loop for only one time,Actually it has to do for 2 times.

Re: Problem with do while

Anonymous — Sun, 02 Aug 2015 08:52:59 GMT

Hi my logic is correct .It has to break the data into week data .

let my start date is 25th june 2015 , and end date is 30th june 2015it has to fetch data from weekstart of 25th and it should fetch data of weekend of 30th june.

I am fetching records correctly but my loop is not running correctly.my loop shouldn't cross the enddate.

Re: Problem with do while

Anonymous — Sun, 02 Aug 2015 09:29:56 GMT

My loop should exit vweekstart is greater than vweekend

Re: Problem with do while

maxgro — Sun, 02 Aug 2015 10:44:43 GMT

First of all I think you should modify

let vweekstart = num(num#(WeekStart(vStartDateKey)));

let vweekend = num(Num#(WeekStart(vEndDateKey)));

if you want the date

WeekStart(vStartDateKey)

if you want thu num

num(WeekStart(vStartDateKey))

LET vStartDateKey = makedate(2015,06,25);

LET vEndDateKey = MakeDate(2015,06,30);

let vweekstart = WeekStart(vStartDateKey);

let vweekend = WeekStart(vEndDateKey);

DO WHILE weekstart(vStartDateKey)<=Weekend(vEndDateKey)

let vnewweekend =date(date#(vweekstart)+6);

trace vweekstart=$(vweekstart);

trace vweekend=$(vweekend);

trace vnewweekend=$(vnewweekend);

//FCT_SALES:

//SELECT

//DATE_KEY,

//SALES_DATE,

//STORE_KEY,

//DEPT_KEY

//WHERE

//DATE_KEY BETWEEN '$(vweekstart)' AND '$(vnewweekend)';

let file = Year((vweekstart))&week((vweekstart));

trace file= $(file);

//Store and Drop Table

//STORE FCT_DAILY_SALES INTO [./Qvd/$(file).QVD];

//DROP TABLE FCT_SALES;

let vweekstart = date(vweekstart+7);

let vStartDateKey = vStartDateKey+7;

LOOP;

and this is the trace

vweekstart=22/06/2015

vweekend=29/06/2015

vnewweekend=28/06/2015

file= 201526

vweekstart=29/06/2015

vweekend=29/06/2015

vnewweekend=05/07/2015

file= 201527

Re: Problem with do while

Kushal_Chawda — Sun, 02 Aug 2015 10:47:21 GMT

Actually this should give you error.

Your table name is FCT_SALES and you are storing FCT_DAILY_SALES into QVD

Re: Problem with do while

Anonymous — Sun, 02 Aug 2015 10:49:57 GMT

hi yes but i had corrected.

Re: Problem with do while

Kushal_Chawda — Sun, 02 Aug 2015 11:33:12 GMT

try like this. its working for me

LET vStartDateKey = num(Date#('2015-06-25','YYYY-MM-DD'));

LET vEndDateKey = num(Date#('2015-06-30','YYYY-MM-DD'));

let vweekstart = num(WeekStart('$(vStartDateKey)'));

let vweekend = num(WeekStart('$(vEndDateKey)')+6);

do while vweekstart <= vweekend

let vnewweekend =num('$(vweekstart)'+6);

FCT_SALES:

SELECT

DATE_KEY,

SALES_DATE,

STORE_KEY,

DEPT_KEY

WHERE

DATE_KEY BETWEEN '$(vweekstart)' AND '$(vnewweekend)';

let file = Year('$(vweekstart)')&week('$(vnewweekend)');

trace file= $(file);

//Store and Drop Table

STORE FCT_SALES INTO [./Qvd/$(file).QVD];

DROP TABLE FCT_SALES;

let vweekstart = num('$(vweekstart)'+7);

LOOP;