Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
liviumac
Creator
Creator

Using variables in WHERE clause

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.

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Instead, You can try this?

Table:

Load date, client_id, product_id,

sales FROM .....;

Right Join (Table)

Load Max(date) as date Resident Table;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

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



talk is cheap, supply exceeds demand
liviumac
Creator
Creator
Author

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

Instead, You can try this?

Table:

Load date, client_id, product_id,

sales FROM .....;

Right Join (Table)

Load Max(date) as date Resident Table;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
liviumac
Creator
Creator
Author

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