topic Re: Put a variable in the WHERE clause in QlikView

Put a variable in the WHERE clause

Tue, 04 Nov 2014 14:22:01 GMT

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!

Re: Put a variable in the WHERE clause

rubenmarin — Tue, 04 Nov 2014 14:30:44 GMT

Hi Ylenia,

If vtest is a string it should be betwen ':

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

Re: Put a variable in the WHERE clause

Tue, 04 Nov 2014 15:37:33 GMT

Hi ruben,

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

Re: Put a variable in the WHERE clause

rubenmarin — Tue, 04 Nov 2014 15:44:33 GMT

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

Re: Put a variable in the WHERE clause

Tue, 04 Nov 2014 15:48:54 GMT

yes vtest has the right value, vtest=xx2015

Re: Put a variable in the WHERE clause

rubenmarin — Tue, 04 Nov 2014 15:55:32 GMT

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

Re: Put a variable in the WHERE clause

gandalfgray — Tue, 04 Nov 2014 15:59:05 GMT

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

Re: Put a variable in the WHERE clause

alex_millan — Tue, 04 Nov 2014 16:09:25 GMT

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,

Re: Put a variable in the WHERE clause

Tue, 04 Nov 2014 17:11:17 GMT

It doesn't work!!!

Re: Put a variable in the WHERE clause

jerem1234 — Tue, 04 Nov 2014 17:36:11 GMT

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!

Re: Put a variable in the WHERE clause

Thu, 06 Nov 2014 16:39:20 GMT

Hi Jeremiah,

this is my script, I also tried to use SET instead of LET....

LET vyear =fieldValue('Year_PC',1); //read the value from a column label (Year_PC=2014)

LET vtest='XX'&($(vyear)+'1');

LOAD A ,

B ,

C ,

Y ,

O ,

AL ,

FROM

Dati\5_BE.xlsx

(ooxml, no labels, header is 7 lines, table is foglio1)

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

I also used the function MsgBox to read the variable and It reads correctly XX2015...

Re: Put a variable in the WHERE clause

alex_millan — Thu, 06 Nov 2014 16:48:41 GMT

As LET evaluates and SET gets a literal, I'd try this way:

LET vyear =fieldValue('Year_PC',1) + 1;

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

LOAD A ,

B ,

C ,

Y ,

O ,

AL ,

FROM

Dati\5_BE.xlsx

(ooxml, no labels, header is 7 lines, table is foglio1)

WHERE O='$(vtest)';

Hope that works,

Regards

Re: Put a variable in the WHERE clause

rubenmarin — Thu, 06 Nov 2014 16:54:34 GMT

You can try first to assure is an variable issue, check if works hardcoding 'XX2015', if not maybe there is an extra blank space in excel or another thing that makes the where clause false

LOAD A ,

B ,

C ,

Y ,

O ,

AL ,

FROM

Dati\5_BE.xlsx

(ooxml, no labels, header is 7 lines, table is foglio1)

WHERE(O='XX2015');

Re: Put a variable in the WHERE clause

Thu, 06 Nov 2014 16:54:35 GMT

no...it's the same!

Re: Put a variable in the WHERE clause

Thu, 06 Nov 2014 16:58:52 GMT

ok, It's a variable issue because with WHERE(O='XX2015') the script works...

Re: Put a variable in the WHERE clause

alex_millan — Thu, 06 Nov 2014 16:59:52 GMT

Yep, try to debug it as Ruben point out, or if possible, send a sample, that would be most helpful.

Regards,

Re: Re: Put a variable in the WHERE clause

rubenmarin — Thu, 06 Nov 2014 17:08:54 GMT

I made an easy sample and it works, check where you have the difference.

Re: Put a variable in the WHERE clause

jerem1234 — Thu, 06 Nov 2014 17:09:50 GMT

Hmm this worked fine for me:

Load * Inline [

Year_PC

2014

2015

2016

];

LET vyear =fieldValue('Year_PC',1); //read the value from a column label (Year_PC=2014)

LET vtest='XX'&($(vyear)+'1');

LOAD A ,

B ,

C ,

Y ,

O ,

AL ,

FROM

C:\Users\kurpatj\Desktop\Dati\5_BE.xlsx

(ooxml, no labels, header is 7 lines, table is foglio1)

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

Sounds like it's probably your data at this point. Check your excel file and make sure the values don't have spaces, extra characters, and the capitilization is right (i.e. make sure its exactly XX2015 and not xx2015, xx 2015, XX 2015, etc.

Hope this helps!

EDIT: Just saw it worked with 'XX2015' hard-coded so you can ignore what I said about your data in the excel.