Using variables in WHERE clause

[liviumac]

Hello,

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:

sales_table:

LOAD

date,

client_id,

product_id,

sales

FROM .....;

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)';

sales_last_table:

LOAD

date,

client_id,

sum(sales)

resident sales_table

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'

thank you.

[Gysbert_Wassenaar]

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:

tmpMaxDate:

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.

[liviumac]

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...

[Anil_Babu_Samineni · Accepted solution]

Instead, You can try this?

Table:

Load date, client_id, product_id,

sales FROM .....;

Right Join (Table)

Load Max(date) as date Resident Table;

[liviumac]

well, that solved the problem, even if by not using variables

in the WHERE clause from the second table I used WHERE date=the name of the date field calculated above in the JOIN statement

thank you