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: 
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
tresesco
MVP
MVP

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

View solution in original post

11 Replies
tresesco
MVP
MVP

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
Author

Please refer the following application and excel file

rbecher
MVP
MVP

Without quotes since vWeek is a number:

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

Astrato.io Head of R&D
Not applicable
Author

Hello Tresesco,

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

Thanks & Regards,

Venkat T

tresesco
MVP
MVP

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.

rbecher
MVP
MVP

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

Astrato.io Head of R&D
Not applicable
Author

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
Author

Hi Tresesco and Ralf,

Please comment on the above.

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

Thanks alot,

Rochelle

tresesco
MVP
MVP

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.