topic Date subtracting in script in QlikView

Date subtracting in script

Wed, 21 Mar 2012 14:16:58 GMT

Hello,

I want to script the difference between Days between a order and a return;

I have the folowing line of script;

interval(RETURN_DATE-ORDER_DATE, 'DD, hh:mm:ss') as RETURN_period

but it gives this error: Undefined function 'interval' in expression.

I attached the qlikview file, the problem is at line 87,

thanks for the help in advance..

Date subtracting in script

Wed, 21 Mar 2012 14:38:21 GMT

The problem is not in the interval-function but in that you are trying to use in in the SQL-statement. The interval-function is a QlikView function which can be used in a LOAD section. To solve your problem, first get data into QlikView by SQL, then use the interval when transforming the data in a LOAD. In you example SQL both values, use a LOAD above like:

RETURN: //FEIT

LOAD

interval(RETURN_DATE-ORDER_DATE, 'DD, hh:mm:ss') as RETURN_period;

SQL SELECT

RI.RETURN_CODE as RETURN_code, //PK

...

OH.SALES_STAFF_CODE&'|'& DATEPART('YYYY', RETURN_DATE ) as LINK_key,

RETURN_DATE,

ORDER_DATE

FROM (RETURNED_ITEM RI

...

Olof Laurin, Senior Consultant at Tacticus AB, http://www.tacticus.se

Date subtracting in script

Wed, 21 Mar 2012 14:59:25 GMT

thanks a lot for the reply, the error is gone, but now I only get - as value in the RETURN_period.. I hoped I would get just number of days.. Any idea what's wrong now?