topic Re: Date Manipulation in QlikView

Date Manipulation

Fri, 18 Jul 2014 08:57:57 GMT

Good morning All,

I am a bit shtumped and not quite sure where I am going wrong.

In a text box on my app if I use the formula =Max(Date) I get 17.7.14. If I modify this to Num(Max(Date)), I get 41837, which tells me my dates are all ok.

However on the script side of things, I am trying to create a variable vMinDate, using Let vMinDate=Num(Min(Date)). I get a script line error. ( In a previous load statement, I have "Date" field).

I am not sure why, can anyone assist?

Thanks

Re: Date Manipulation

sujeetsingh — Fri, 18 Jul 2014 09:03:53 GMT

use

vMinDate=Date#(Min(Date))

Re: Date Manipulation

Fri, 18 Jul 2014 09:14:22 GMT

Thanks sujeetsingh, but unfortunately I am still getting a script line error

Re: Date Manipulation

tresB — Fri, 18 Jul 2014 09:23:39 GMT

Try like:

Load

Max(Date) as Max,

Min(Date) as Min

From <>;

vMaxDate=Date(Peek('Max'));

vMinDate=Date(Peek('Min'));

Re: Date Manipulation

Fri, 18 Jul 2014 09:30:19 GMT

thanks tresesco, this works perfectly, what I don't understand is why when I just put Let vMinDate=Num(Min(Date)), QlikView couldn't simply find the Date field and Min it. Why do we have to call up the field again?

Re: Date Manipulation

tresB — Fri, 18 Jul 2014 09:37:03 GMT

Min() is an aggregation function and when it is used in the script it expects a group by clause in the load statement.

Re: Date Manipulation

sudeepkm — Fri, 18 Jul 2014 09:38:27 GMT

at script level when you want to get a field value from a table then you need to use peek functions. as suggested by tresesco.

if you already have a table loaded which has date field as orderdate then you can create another table like below

temptab:

load max(orderdate) as mxdt resident maintable; // this table will have only one record containing the max date

vMxDT= peek('mxdt',0,'temptab'); // table name not required if it is there just before this script

Re: Date Manipulation

Fri, 18 Jul 2014 09:41:30 GMT

Thank you, much appreciated