topic Re: Get max date from the script? in QlikView

Get max date from the script?

Tue, 26 May 2015 18:54:24 GMT

I need to pick the max(saleDate ) from the script, not from the the frontend variable using max(salesdate) because then it becomes dynamic .

It should not change, it should should only after reload, other remain static.

My calender has more than 100 million records.

I want to get the max date without compromising the performance of my dashboard.

Clever_Anjos — Tue, 26 May 2015 18:56:53 GMT

LOAD

max(saleDate )

FROM yourqvd.qvd(qvd)

swarup_malli — Tue, 26 May 2015 18:57:38 GMT

Try creating a temp table in the script ,get the max date then drop it

/**Creating a temp table to get max date**/

ALL_SALEDATE:
Load FieldValue('DateField',IterNo()) as DateField_Max_Date_Time
AutoGenerate(1)
While not Isnull(FieldValue('DateField',IterNo()));

MAX_SDATE:
Load Max(DateField_Max_Date_Time) as Your_Max_Date

resident ALL_SALEDATE:;
Drop table ALL_SALEDATE; //no longer needed
exit script;

Tue, 26 May 2015 19:05:19 GMT

Hi, Create a table that stores Max date and then use the Peek function to convert field into variable.

maxgro — Tue, 26 May 2015 19:15:20 GMT

YourTable:

load date(makedate(2015) + floor(rand()*1000)) as SaleDate AutoGenerate 100;

MaxTable: load max(SaleDate) as MaxSaleDate Resident YourTable;

LET vMaxSaleDate = date(Peek('MaxSaleDate'));

drop table MaxTable;