topic Re: Resolving Date issue in QlikView

Resolving Date issue

Anonymous — Wed, 29 Jul 2015 11:42:35 GMT

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;

Re: Resolving Date issue

Colin-Albert — Wed, 29 Jul 2015 11:50:42 GMT

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

Re: Resolving Date issue

avinashelite — Wed, 29 Jul 2015 11:52:21 GMT

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)

Re: Resolving Date issue

Anonymous — Wed, 29 Jul 2015 12:07:25 GMT

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

What does this mean ?

Thanks

Re: Resolving Date issue

Kushal_Chawda — Wed, 29 Jul 2015 12:18:06 GMT

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;

Re: Resolving Date issue

Anonymous — Wed, 29 Jul 2015 13:20:15 GMT

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

Re: Resolving Date issue

avinashelite — Wed, 29 Jul 2015 13:38:32 GMT

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

Re: Resolving Date issue

Kushal_Chawda — Wed, 29 Jul 2015 13:42:35 GMT

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;

Re: Resolving Date issue

Anonymous — Wed, 29 Jul 2015 16:43:51 GMT

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

Re: Resolving Date issue

Anonymous — Wed, 29 Jul 2015 16:45:07 GMT

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

Thanks

Re: Resolving Date issue

Kushal_Chawda — Wed, 29 Jul 2015 16:46:28 GMT

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

Re: Resolving Date issue

Colin-Albert — Wed, 29 Jul 2015 16:57:18 GMT

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

Re: Resolving Date issue

Anonymous — Thu, 30 Jul 2015 05:30:14 GMT

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

Re: Resolving Date issue

sasiparupudi1 — Thu, 30 Jul 2015 05:51:50 GMT

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

Re: Resolving Date issue

Anonymous — Thu, 30 Jul 2015 06:16:05 GMT

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

Re: Resolving Date issue

sasiparupudi1 — Thu, 30 Jul 2015 06:27:09 GMT

try

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

Re: Resolving Date issue

Anonymous — Thu, 30 Jul 2015 06:45:01 GMT

i tried

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

still not giviing correct results