Problem with do while

[Anonymous]

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;

[sunny_talwar]

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

LOOP;

I think you are changing vweekstart instead of vStartDateKey

[sunny_talwar]

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

[Anonymous]

Hi

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;

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

Hi

My loop should exit vweekstart is greater than vweekend

[maxgro · Accepted solution]

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

vweekstart=22/06/2015

vweekend=29/06/2015

vnewweekend=28/06/2015

file= 201526

vweekstart=29/06/2015

vweekstart=29/06/2015

vweekend=29/06/2015

vnewweekend=05/07/2015

file= 201527

[Kushal_Chawda]

Actually this should give you error.

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

[Anonymous]

hi yes but  i had corrected.