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: 
davidl60
Contributor II
Contributor II

Exclude records in loading script

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

RefField1 LYField2 LYField1 TYField2 TYVariance Field1Variance Field2
A1.23.81.253.74.16%-2.63%
B0.0154.627.854.6277900%0%
C0087.398.5--
D059.631.3261.48-2.54%
E3.34505.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


1 Solution

Accepted Solutions
atoz1158
Creator II
Creator II

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

View solution in original post

4 Replies
atoz1158
Creator II
Creator II

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

Miguel_Angel_Baeyens

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.

davidl60
Contributor II
Contributor II
Author

Hi Adrian,

Thanks. It works.

Simply AND instead of OR...

David

davidl60
Contributor II
Contributor II
Author

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