Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Put a variable in the WHERE clause

Hi, I have a problem with the WHERE clause.

I'd like to filter my data not just with a field name but with a variable.

In particluar i have done a viariable vtest='XX'&($(vyear)+'1'), (where vyear=2015 so vtest=XX2015)

and i want to put this variable in the WHERE like: WHERE(O=$(vtest));

I've done something like that with a variable made just with numbers and it works fine, but I can't make this work maybe because the variable is a string.

Any tips?

Thanks in advance!

Tags (2)
17 Replies

Re: Put a variable in the WHERE clause

Hi Ylenia,

If vtest is a string it should be betwen ':

WHERE(O='$(vtest)');

Not applicable

Re: Put a variable in the WHERE clause

Hi ruben,

With WHERE(O='$(vtest)') it doesen't show any value....

Re: Put a variable in the WHERE clause

Hi, can you check vtest has a value? After reload you can open variable panel to check the value or in log.

Maybe vtest should be:

vtest='XX'& '$(vyear)';

Not applicable

Re: Put a variable in the WHERE clause

yes vtest has the right value, vtest=xx2015

Re: Put a variable in the WHERE clause

OK, then I don't know, it should work, in log you can check the query executed by script and see if it's ok.

Also, try comment the "LET vtest" line and fix the value to test:

LET vtest='XX2015';

gandalfgray
Valued Contributor

Re: Put a variable in the WHERE clause

Hi

You should not have any quotes around the $(var) part

example:

Let vYear=2015;

Load

...

FROM the_table

WHERE (O=$(vYear));

(this works regardles if selecting from sql-database or loading from qvd)

Edit:

If you would use strings in your Where statement (and not use variables) you will need to put quotes around the strings,

but that has nothing to do with the use of a variable.

Example:

Hardcoded:

Load

..

FROM a_table

WHERE type='valid'

and year=2014;

With variables:

Let vType='valid';

Let vYear=2014;

..

Load

..

FROM a_table

WHERE type='$(vType)'

and year=$(vYear);

alex_millan
Contributor III

Re: Put a variable in the WHERE clause

As they pointed before, assuming the content of the variable is a string:

To declare the variable:      LET vtest = 'XXXXXXX'

To use in the query:     where [Field] = '$(vtest)'

In case you need to concatenate the variable:     where [Field] = '$(vtest)Restoftheneededstring'

Regards,

Not applicable

Re: Put a variable in the WHERE clause

It doesn't work!!!

jerem1234
Valued Contributor II

Re: Put a variable in the WHERE clause

You have to make sure your variables have the correct value, using the correct Let or Set to declare a variable. Certainly you can use a string in a where statement. Please see attached example. If it is not working and you cannot solve the problem by debugging yourself, then please either make an example demonstrating your problem or attach your dashboard. Could be something more going on (i.e. the functions you use to create variables, the script itself, or other things that can't be predicted).

The script I used in case you can't access the dashboard is:

Set vYear = 2015;

Let vTest = 'XX' & $(vYear);

Table1:

Load * Inline [

Group, O, Value

A, XX2013, 25

A, XX2014, 67

A, XX2015, 90

A, XX2016, 46

]

where O = '$(vTest)';

Hope this helps!

Community Browser