topic Re: How to use Variable in Where Clause in QlikView

How to use Variable in Where Clause

Mon, 07 Oct 2013 11:26:25 GMT

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

Re: How to use Variable in Where Clause

tresB — Mon, 07 Oct 2013 11:31:46 GMT

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

Re: How to use Variable in Where Clause

Mon, 07 Oct 2013 11:34:23 GMT

Please refer the following application and excel file

Re: How to use Variable in Where Clause

rbecher — Mon, 07 Oct 2013 12:23:17 GMT

Without quotes since vWeek is a number:

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

Re: How to use Variable in Where Clause

Tue, 08 Oct 2013 07:09:04 GMT

Hello Tresesco,

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

Thanks & Regards,

Venkat T

Re: How to use Variable in Where Clause

tresB — Wed, 09 Oct 2013 05:14:33 GMT

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.

Re: How to use Variable in Where Clause

rbecher — Wed, 09 Oct 2013 06:29:37 GMT

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

Re: How to use Variable in Where Clause

Wed, 09 Oct 2013 06:41:16 GMT

Hi Venkat,

What you can do is:

ABC:

Load

Week

From ABC.QVD;

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

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

Re: How to use Variable in Where Clause

Wed, 09 Oct 2013 06:42:30 GMT

Hi Tresesco and Ralf,

Please comment on the above.

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

Thanks alot,

Rochelle

Re: How to use Variable in Where Clause

tresB — Wed, 09 Oct 2013 07:29:35 GMT

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.

Re: How to use Variable in Where Clause

rbecher — Wed, 09 Oct 2013 08:55:39 GMT

Yeah, this might work mostly in a LOAD Where clause but maybe not in a SQL Select Where clause..

Re: How to use Variable in Where Clause

tresB — Wed, 09 Oct 2013 09:19:12 GMT

Thanks Ralf.