Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danielrogra
Creator
Creator

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

10 Replies
sunny_talwar

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
Creator
Creator
Author

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

sunny_talwar

How about this

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

danielrogra
Creator
Creator
Author

Thank you very much.

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

Regards

danielrogra
Creator
Creator
Author

Any other idea? Thanks in advance

sunny_talwar

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

LET vMaxDate = peek('MaxDate');

danielrogra
Creator
Creator
Author

Sure Sunny.

Temp:

Load date as MaxDate;

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

sunny_talwar

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
Creator
Creator
Author

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