let vMaxDat = 'max(date)'; doesn't calculate anything. It assigns the string 'max(date)' to the variable vMaxDat. That will not work in the WHERE clause. You first need to calculate the max date before you can use it:
LOAD max(Date) as MaxDate RESIDENT sales_table;
LET vMaxDat = num(peek('MaxDate'));
DROP TABLE tmpMaxDate;
Now vMaxDat has a value you can use in the WHERE clause.
thanks for the answer, unfortunately I did not get it to work: the script runs, but there is no value in the date field of the second table
how exactly is the syntax for the WHERE clause using your temporary table?
also, you say that let vMaxDat = 'max(date)' does not calculate anything, but if I put it in a KPI object I see the correct date
I have a feeling that this has something to do with the way the date is formatted in the original table: it is formatted as MMM-YY via the script and I do not think that it is stored as a number...