topic Re: Exclude records in loading script in QlikView

Exclude records in loading script

davidl60 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Exclude records in loading script

atoz1158 — Mon, 10 Sep 2018 16:20:24 GMT

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

Re: Exclude records in loading script

Miguel_Angel_Baeyens — Mon, 10 Sep 2018 16:22:38 GMT

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.

Re: Exclude records in loading script

davidl60 — Tue, 11 Sep 2018 10:04:39 GMT

Hi Adrian,

Thanks. It works.

Simply AND instead of OR...

David

Re: Exclude records in loading script

davidl60 — Tue, 11 Sep 2018 10:05:54 GMT

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