topic Re: Variable to use in a query in App Development

Variable to use in a query

jjustingkm — Thu, 08 May 2025 18:57:20 GMT

I have a SQL created like below. I am trying to get a variable to use in the SQL. Can you check this would work?

variable V = IF( WeekDay(Today()) = 'Sat') Then 1000 else 365

Select * from SALES

where sale_date < sysdate() - V

Re: Variable to use in a query

chriscammers — Thu, 08 May 2025 21:16:20 GMT

Injecting values into your sql statement is actually very easy and can lead you to all kinds of dynamic sql statements. The case you mentioned would look something like below.

//set the value of your variable Let vWhereDate = Date(GetDate() - IF( WeekDay(Today()) = 'Sat'),1000,365)); //Insert the value into your sql statement SQL Select * from SALES where Sale_Date < '$(vWhereDate)';

The key to the whole thing is to set the variable so it gives you the value you want in the query. In this case instead of calculating part of it in the where clause i did it in the variable expression. the Dollar Sign expansion in the sql statement inserts the value of the variable into your sql statement.

Re: Variable to use in a query

Anil_Babu_Samineni — Fri, 09 May 2025 02:42:53 GMT

I don't think GetDate() is Qlik function. Perhaps we can try like below

Let V = Date(Today()-IF( WeekDay(Today()) = 'Fri', 1000, 365));
Trace '$(V)';

Select * from SALES where sale_date < '$(V)';

Make sure date format in aligned for both sale_date and variable.