Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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

View solution in original post

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