Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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...
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
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');
no...it's the same!
ok, It's a variable issue because with WHERE(O='XX2015') the script works...
Yep, try to debug it as Ruben point out, or if possible, send a sample, that would be most helpful.
Regards,
I made an easy sample and it works, check where you have the difference.
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.