Resolving Date issue

[Anonymous]

Hi All,

Below is my query .The logic is to genearete weekly qvds that is fine.but the problem is with date format.it is not recognizing the date and doesn't fetching any data due to date.

Help me out  .Is some thing should be added to date format.

LET vStartDateKey  = '20150601' ;

LET vEndDateKey    = '20150628' ;

let vweekstart =WeekStart(Date#(vStartDateKey,'YYYYMMDD'));

let vweekend =WeekStart(Date#(vEndDateKey,'YYYYMMDD'));

do while vweekstart<=vweekend

// Pulling Sales Data from FCT_BEM_SALES_D

FCT_DAILY_SALES:

//1. FCT Sales -CY

SELECT

  *

FROM

    FCT_SALES_D fs

    INNER JOIN DIM_ITEM di

        ON   di.ITEM_KEY = fbsd.ITEM_KEY

WHERE

    fs.SALES_DATE BETWEEN $(vweekstart) AND $(vweekstart)+6;

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

trace file= $(file);

// Store and Drop Table

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

DROP TABLE FCT_SALES;

let vweekstart = date(vweekstart+7);

loop;   

[Kushal_Chawda]

Yes, try with num function in each variable instead of date function

[Colin-Albert]

Use Floor() to round the date to an integer and remove the time component.

[Anonymous]

Hi

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

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

let vweekstart = num(WeekStart(vStartDateKey));

let vweekend = num(WeekStart(vEndDateKey));

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

do while vweekstart<=vweekend

FCT_DAILY_SALES:

//1. FCT Sales -CY

sql SELECT

    *

FROM

    FCT_SALES_D fs

WHERE

    fs.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](qvd);

DROP TABLE FCT_DAILY_SALES;

let vweekstart = vweekstart+7;

loop;   

My DATE_KEY format is 20150601 and it is integer.

But still i cannot fetch the records .

Thanks

[sasiparupudi1]

HI Rgv

makedate function will create a date in the qlikview default format. unless your qlikview default format is set to YYYYMMDD, Date conversions wont work

I suggest you debug the code and see what is in the variables so that you can pinpoint to the problem. Otherwise, post your application here.

HTH

Sasi

[Anonymous]

Hi

i could pull the records for 1st week .when i am trying to fetch the record of 2nd week it is not fetching.

i tried

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

i think problem is in the above statement.

Thanks

[sasiparupudi1]

try

num(date('$(vweekstart)')+7;

[Anonymous]

Hi

i tried

num(date('$(vweekstart)')+7);

still not giviing correct results