I've read several other topics on the same issue but the solutions did not work for me so I kindly ask for your help.
I have a sales table which I load with the following statement:
what I want is to create a separate table storing total sales per customer as of the latest date
in order to do that, I first define a variable which calculates the maximum date:
let vMaxDat = 'max(date)';
WHERE date = $(vMaxDat)
group by date, client_id;
I have tried Set instead of Let and any combination of commas and parenthesis in the WHERE clause, however I never get any value in the date field from the sales_last_table and therefore I get no sales
the variable does store the correct value (I see it in a sheet) and if I use the actual date in WHERE clause, it works, but not with a variable
however I need to use the variable in order to eliminate the need to manually change the date at each script run
I should mention that the date in the original table is formatted as 'MMM-YY'
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.