topic Re: Variable Errors and Issues in QlikView

Variable Errors and Issues

MalcolmCICWF — Tue, 26 May 2015 20:36:04 GMT

I have a report that recently started failing with the below error. I am not sure why, but maybe has something to do with the variables? Does anyone have any ideas as I head into this problem?

Here is the chunk of code that is no longer working. Maybe something with the DATEADD?

let varMinDate =num(Peek('DATEADD',0,'STCDAILY')) ;
let varMaxDate =num(Peek('DATEADD',-1,'STCDAILY')) ;
let vToday=num(Today()) ;

TempCalender:
LOAD
$(varMinDate) + IterNo()-1 as Num,
Date($(varMinDate)+IterNo()-1) as TempDate
AUTOGENERATE 1 WHILE $(varMinDate) + IterNo()-1<=$(varMaxDate) ;

MASTERCALENDER:
LOAD
TempDate as DATEADD,
WEEK(TempDate) as WEEK,
YEAR(TempDate) as YEAR,
MONTH(TempDate) as MONTH,
DAY(TempDate) as DAY,
WEEKDAY(TempDate) as WEEKDAY,
DATE(MONTHSTART(TempDate),'MMM-YYYY') AS MONTHYEAR

RESIDENT TempCalender

ORDER BY TempDate ASC ;

DROP TABLE TempCalender ;

Re: Variable Errors and Issues

sunny_talwar — Tue, 26 May 2015 20:39:44 GMT

Can you check if QlikView is interpretting DATEADD as a number/date or Text String? If it is being interpreted as Text then you will run into a error like the one you have.

You said it was working earlier? What changed?

Best,

Sunny

Re: Variable Errors and Issues

Clever_Anjos — Tue, 26 May 2015 20:45:24 GMT

This expression is return NULL

let varMinDate =num(Peek('DATEADD',0,'STCDAILY')) ;

Re: Variable Errors and Issues

stevedark — Tue, 26 May 2015 20:46:48 GMT

At the point your script is failing there is no value in the variables varMinDate or varMaxDate.

I would recommend putting an Exit Script after the PEEK statements and view what is in the STCDAILY table. My guess is on the last row there is either an empty field, or a value that can not be translated as a number. It is also possible that STCDAILY doesn't have any rows in it.

You don't show the code where STCDAILY is loaded, so it is not clear whether that needs a change. It may be a WHERE statement needs to be added, or you need to check where it is loading from.

Cheers,

Steve