Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
danielrogra
Contributor

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
Highlighted

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

Highlighted
danielrogra
Contributor

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

Highlighted

Re: Using variable in SQL where clause

How about this

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

Highlighted
danielrogra
Contributor

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

Highlighted
danielrogra
Contributor

Re: Using variable in SQL where clause

Any other idea? Thanks in advance

Highlighted

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

Highlighted
danielrogra
Contributor

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)

Highlighted

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?

Highlighted
danielrogra
Contributor

Re: Using variable in SQL where clause

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