Script Error

[#1]

Hi

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;

[Anonymous]

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...

[#3]

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

[#4 · Accepted solution]

No, you have to use it like this:

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

[#5]

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;

[Anonymous]

Not yet.
See answers from Augustin Peyridieux and Ajay Prabhakaran