topic Re: Script Error in QlikView

Script Error

Wed, 14 May 2014 15:43:17 GMT

Can some one please help. When I run the following script I get an error because the NEW_CALL_DATE cannot be founnd. I've moved this to the first and last line and still get the problem.

LOAD

"CALL_DATE",

num(CALL_DATE) as NEW_CALL_DATE,

num(today()-366) as LAST_YEAR,

"CALL_NUMBER",

CATEGORY,

SERIAL as VISIT_SERIAL,

If (CATEGORY = 'EM' and NEW_CALL_DATE > LAST_YEAR,'Y','N') as EM_LAST_YEAR;

SQL SELECT "CALL_DATE",

"CALL_NUMBER",

CATEGORY,

SERIAL

FROM VISITS;

Re: Script Error

Anonymous — Wed, 14 May 2014 15:50:29 GMT

This error happens because you try to use NEW_CALL_DATE in this line

If (CATEGORY = 'EM' and NEW_CALL_DATE > LAST_YEAR,'Y','N') as EM_LAST_YEAR

And, LAST_YEAR also doesn't exist yet...

Re: Script Error

Wed, 14 May 2014 15:52:04 GMT

But haven't I created these with the num function above?

Re: Script Error

Wed, 14 May 2014 15:55:44 GMT

No, you have to use it like this:

If (CATEGORY = 'EM' and num(CALL_DATE) > num(today()-366),'Y','N')

Re: Script Error

Wed, 14 May 2014 15:56:10 GMT

As mentioned by Michael NEW_CALL_DATE and LAST_YEAR are newly created fields and hence wont be available until the table is fully loaded once.

So to fix it you can do another preload (totally two preloads here).

Load

If (CATEGORY = 'EM' and NEW_CALL_DATE > LAST_YEAR,'Y','N') as EM_LAST_YEAR;

LOAD

"CALL_DATE",

num(CALL_DATE) as NEW_CALL_DATE,

num(today()-366) as LAST_YEAR,

"CALL_NUMBER",

CATEGORY,

SERIAL as VISIT_SERIAL;

SQL SELECT "CALL_DATE",

"CALL_NUMBER",

CATEGORY,

SERIAL

FROM VISITS;

Re: Script Error

Anonymous — Wed, 14 May 2014 15:59:30 GMT

Not yet.
See answers from Augustin Peyridieux and Ajay Prabhakaran