Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

danielrogra
New Contributor III

Using variable in SQL where clause

Hi all

I'm facing an issue I can't solve although there is some information on internet....

I want to create a variable with contains just a date like this:

LET vMaxDate = peek('MaxDate');

Then, I want to use it in a SQL where clause, like this:

and datediff(mm,start_date,$(vMaxDate)) between 0 and 11

But it's not working...I've also tried doing the following:

and datediff(mm,start_date,'$(vMaxDate)') between 0 and 11

But still not working....I'm sure there is something I'm missing but I am not able to see it.

Does anyone know how to fix it? Thanks in advance

Daniel

Tags (2)
10 Replies
MVP
MVP

Re: Using variable in SQL where clause

What is the format of vMaxDate... may be you need TO_CHAR() or TO_DATE() to help with the interpretation within the SQL statement

danielrogra
New Contributor III

Re: Using variable in SQL where clause

Thanks for your quick reply

I've tried using to_date like follows: datediff(mm,start_date,To_date('$(vMaxDate)')) between 0 and 11

but still not working.....The variable is like this:

Captura232.PNG

MVP
MVP

Re: Using variable in SQL where clause

How about this

DATEDIFF(mm,start_date, TO_DATE('$(vMaxDate)', 'DD/MM/YYYY H:MISmiley FrustratedS')) between 0 and 11

danielrogra
New Contributor III

Re: Using variable in SQL where clause

Thank you very much.

Unfortunately it's still not working...Maybe I'm doing something wrong in the peek function?

Regards

danielrogra
New Contributor III

Re: Using variable in SQL where clause

Any other idea? Thanks in advance

MVP
MVP

Re: Using variable in SQL where clause

Can you share the script where you create the field MaxDate to use within Peek() function?

LET vMaxDate = peek('MaxDate');

danielrogra
New Contributor III

Re: Using variable in SQL where clause

Sure Sunny.

Temp:

Load date as MaxDate;

select distinct date from tableA a inner join TableB b on ....; (This load just one date)

MVP
MVP

Re: Using variable in SQL where clause

Try this

Temp:

Load Date(date, 'DDMMMYYYY') as MaxDate;

select distinct date from tableA a inner join TableB b on ....;


LET vMaxDate = peek('MaxDate');

and then use this

and datediff(mm,start_date,'$(vMaxDate)') between 0 and 11

DDMMMYYYY is my guess format, are you using ORACLE database?

danielrogra
New Contributor III

Re: Using variable in SQL where clause

Not working....I've used this format: 'DD/MM/YYYY H:MISmiley FrustratedS', the same that in the database...