Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to use Variable in Where Clause

Hello All,

Need your help very urgent.

i have a something like below,

Load

Week

From ABC.QVD;

vWeek=max(Week)

Sellout:

Load *

From XYZ.QVD where Week<=$(vWeek);

When i run the above script, it says field not found.

Need your help on how to use variable in Where clause.

Thanks for your help

Thanks & Regards,

Venkat T

1 Solution

Accepted Solutions
MVP
MVP

Re: How to use Variable in Where Clause

Try something like:

Table1:

Load

Max(Week) as MaxWeek    // Note

From ABC.QVD;

vWeek=Peek('MaxWeek')   // Note

Sellout:

Load *

From XYZ.QVD where Week<= '$(vWeek)';   // Note, single quotes

11 Replies
MVP
MVP

Re: How to use Variable in Where Clause

Try something like:

Table1:

Load

Max(Week) as MaxWeek    // Note

From ABC.QVD;

vWeek=Peek('MaxWeek')   // Note

Sellout:

Load *

From XYZ.QVD where Week<= '$(vWeek)';   // Note, single quotes

Not applicable

Re: How to use Variable in Where Clause

Please refer the following application and excel file

MVP & Luminary
MVP & Luminary

Re: How to use Variable in Where Clause

Without quotes since vWeek is a number:

... where Week<=$(vWeek);

Not applicable

Re: How to use Variable in Where Clause

Hello Tresesco,

Thank you so much, it worked. thanks for your help.

Thanks & Regards,

Venkat T

MVP
MVP

Re: How to use Variable in Where Clause

Hello Becher,

Thanks for your input. I have seen that numeric comparison works with and without single quotes while string comparison demands single quotes. Therefore, I usually follow the common one. If you have come across any scenario when single quotes don't work, please let me know, it would enrich my experience.

Thanks.

MVP & Luminary
MVP & Luminary

Re: How to use Variable in Where Clause

Tresesco,

how you can be sure in every case that a data comparison works with two different data types? There are a lot of examples where it wont work.. It's easy and secure to use always the right data type.

- Ralf

Not applicable

Re: How to use Variable in Where Clause

Hi Venkat,

What you can do is:

1)

ABC:

Load

Week

From ABC.QVD;

2) Define a variable :vWeek=max(Week)

3)

Temp:

Load Week resident ABC whereWeek<=$(vWeek);

inner join(Temp)

//Sellout:

Load *

From XYZ.QVD ;

' Week ' is present in table ABC and not XYZ,so it s not going to find that field anyway.

Try the above,hope this works.

please let me know its result.

Thanks,

Rochelle

Not applicable

Re: How to use Variable in Where Clause

Hi Tresesco and Ralf,

Please comment on the above.

Please let me know your suggestions and views on the same.

Thanks alot,

Rochelle

MVP
MVP

Re: How to use Variable in Where Clause

Ralf,

It's not that I don't agree. Might be, I am missing something silly. So request you to give a hint of where it doesn't work.