Put a variable in the WHERE clause

[#1]

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!

[rubenmarin]

Hi Ylenia,

If vtest is a string it should be betwen ':

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

[#3]

Hi ruben,

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

[rubenmarin]

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)';

[#5]

yes vtest has the right value, vtest=xx2015

[rubenmarin]

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]

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]

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,

[#9]

It doesn't work!!!

[jerem1234]

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!