Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

17 Replies
Not applicable
Author

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  ,

     AM 

    

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...

alex_millan
Creator III
Creator III

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  ,

     AM

   

FROM

Dati\5_BE.xlsx

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

WHERE O='$(vtest)';

Hope that works,

Regards

rubenmarin

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  ,

     AM 

FROM

Dati\5_BE.xlsx

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

WHERE(O='XX2015');

Not applicable
Author

no...it's the same!

Not applicable
Author

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

alex_millan
Creator III
Creator III

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

Regards,

rubenmarin

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

jerem1234
Specialist II
Specialist II

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  ,

     AM

  

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.