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;   

[Colin-Albert]

Run your script in debug mode and step to the SQL query to see how the date variables are being interpreted in the script window

You may need to add quotes around your variables, and create a new variable for the end date rather than using an expression.

Try something like this

WHERE

    fs.SALES_DATE BETWEEN '$(vweekstart)' AND '$(vnewvariable)'

[avinashelite]

hi you cannot initialize the date like this

LET vStartDateKey  = '20150601' ;

LET vEndDateKey    = '20150628'

and apply the date function on top of that

you should use of  the makedatefunction  try like this ;

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

LET vEndDateKey    = makedate(2015,06,28)

[Anonymous]

hi

While i am query dates are working fine but timestamp inconsistent error and INT4 error has encountered .

What does this mean ?

Thanks

[Kushal_Chawda · Accepted solution]

Hi,

Date format of SALES_DATE and  variables vweekstart &  vweekstart should be same.

If your SALES_DATE format is 'DD-MM-YYYY' change the code like below

LET vStartDateKey  = '20150601' ;

LET vEndDateKey    = '20150628' ;

let vweekstart =date(WeekStart(Date#('$(vStartDateKey)','YYYYMMDD')),'DD-MM-YYYY');

let vweekend =date(WeekStart(Date#('$(vEndDateKey)','YYYYMMDD')),'DD-MM-YYYY');

do while vweekstart<=vweekend

let vWeekEndNew = date('$(vweekstart)'+6,'DD-MM-YYYY')

// 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 '$(vWeekEndNew )';

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;  

[Anonymous]

Hi ,

this is new code

my datasource is in this format YYYYMMDD

I have tried the below code but it not fetching records.

let vnewweekend =   date('$(vweekstart)'+6,'YYYYMMDD');

here is my full code

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

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

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

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

let vnewweekend =   date(vweekstart+6,'YYYYMMDD');

do while vweekstart<=vweekend

FCT_SALES:

sql SELECT

  *

FROM

    FCT_BEM_SALES_D fbsd

    INNER JOIN DIM_ITEM di

        ON   di.ITEM_KEY = fbsd.ITEM_KEY

WHERE

    fbsd.DATE_KEY BETWEEN '$(vweekstart)' AND '$(vnewweekend)'

GROUP BY

     fbsd.DATE_KEY,

    fbsd.SALES_DATE,

    fbsd.STORE_KEY,

    di.DEPT_KEY,

    fbsd.SECTION_KEY,

    di.FAMILY_KEY,

    di.SUBFAMILY_KEY ;

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 = date(vweekstart+7);

loop;   

.Thanks

[avinashelite]

Does your SALES_DATE  has time with it ??

if so your start and end need to be in the same format

or convert the SALES_DATE to date format as the variable

[Kushal_Chawda]

Try,

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

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

let vweekstart =date(WeekStart('$(vStartDateKey)'),'YYYYMMDD');

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

let vnewweekend =   date('$(vweekstart)'+6,'YYYYMMDD');

do while vweekstart<=vweekend

FCT_SALES:

sql SELECT

  *

FROM

    FCT_BEM_SALES_D fbsd

    INNER JOIN DIM_ITEM di

        ON   di.ITEM_KEY = fbsd.ITEM_KEY

WHERE

    fbsd.DATE_KEY BETWEEN  '$(vweekstart)' AND  '$(vnewweekend)'

GROUP BY

     fbsd.DATE_KEY,

    fbsd.SALES_DATE,

    fbsd.STORE_KEY,

    di.DEPT_KEY,

    fbsd.SECTION_KEY,

    di.FAMILY_KEY,

    di.SUBFAMILY_KEY ;

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 = date('$(vweekstart)'+7,'YYYYMMDD');

loop;  

[Anonymous]

Hi

i am fetching the data with date_key which is integer type in database.i am using date type to fetch the records .i think i am wrong there. i think i have to convert date to number .will Num() help me ??

Thanks

[Anonymous]

Hi

I am using date_key to fetch the records. and it is integer type.How to type cast in qlikview against database?

Thanks