Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Morning,
I've got an issue with loading script.
In order to compare like for like, I want to exclude records where some specific fields are equal to 0 or 0.01.
I tried several things I found on the forum but they don't work. It's probably only a syntax issue but can't sort it.
Basically, I've got an excel file with something like
Ref | Field1 LY | Field2 LY | Field1 TY | Field2 TY | Variance Field1 | Variance Field2 |
---|---|---|---|---|---|---|
A | 1.2 | 3.8 | 1.25 | 3.7 | 4.16% | -2.63% |
B | 0.01 | 54.6 | 27.8 | 54.6 | 277900% | 0% |
C | 0 | 0 | 87.3 | 98.5 | - | - |
D | 0 | 59.6 | 31.32 | 61.48 | - | 2.54% |
E | 3.34 | 5 | 0 | 5.5 | -100% | 10% |
I want to import only the records where Field1 LY or Field2 LY or Field1 TY are not 0. Records A and B from the above table.
I tried:
Load *
From myExcelFile
Where not(Field1 LY) like '0' or not(Field2 LY) like '0' or not(Field1 TY) like '0';
Where not(Field1 LY) like 0 or not(Field2 LY) like 0 or not(Field1 TY) like 0;
Where Field1 LY <> 0 or Field2 LY <> 0 or Field1 TY <> 0;
Where num(Field1 LY) <> 0 or num(Field2 LY) <> 0 or num(Field1 TY) <> 0;
I don't understand what's going wrong.
I would be greatful of any help.
Thank you all.
David
Hi
To only get records A & B you need to use AND as follows
Where Field1 LY <> 0 AND Field2 LY <> 0 AND Field1 TY <> 0;
Cheers
Adrian
Hi
To only get records A & B you need to use AND as follows
Where Field1 LY <> 0 AND Field2 LY <> 0 AND Field1 TY <> 0;
Cheers
Adrian
If that's a copy paste from your script, make sure you only use semicolon ";" after the last line of the statement and that you are using AND instead of several WHERE lines as Adrian explains above.
Hi Adrian,
Thanks. It works.
Simply AND instead of OR...
David
Hi Miguel,
All the WHERE lines were examples of what I tried., but I have only one in my script.
and yes it works with AND.
Thank you
David