How to use Variable in Where Clause

[#1]

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

[tresesco · Accepted solution]

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

[#3]

Please refer the following application and excel file

[rbecher]

Without quotes since vWeek is a number:

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

[#5]

Hello Tresesco,

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

Thanks & Regards,

Venkat T

[tresesco]

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]

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

[#8]

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

[#9]

Hi Tresesco and Ralf,

Please comment on the above.

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

Thanks alot,

Rochelle

[tresesco]

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.