Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try like:
Load
Max(Date) as Max,
Min(Date) as Min
From <>;
vMaxDate=Date(Peek('Max'));
vMinDate=Date(Peek('Min'));
use
vMinDate=Date#(Min(Date))
Thanks sujeetsingh, but unfortunately I am still getting a script line error
Try like:
Load
Max(Date) as Max,
Min(Date) as Min
From <>;
vMaxDate=Date(Peek('Max'));
vMinDate=Date(Peek('Min'));
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?
Min() is an aggregation function and when it is used in the script it expects a group by clause in the load statement.
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
Thank you, much appreciated