Get max date from the script?

[#1]

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]

LOAD

     max(saleDate )

FROM yourqvd.qvd(qvd)

[swarup_malli · Accepted solution]

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;

[#4]

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

[maxgro]

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;