Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

tomtastic
New Contributor II

Exclude rows by value from load

Hi,

I want to exclude all the rows which contain value "FO" in Field `Type` (Syntax nr.34). I found some similar threads and tried the solutions which were provided there but I keep getting an error when loading the data or the data loads incorrectly.

Thanks in advance for taking the time to reply.

Attempt 1: replace `Type` with  where `Type` <> 'FO',       this leads to an error during loading

Attempt 2: replace `Type` with  where (Type) <> 'FO',       this leads to an error during loading

Attempt 3: replace `Type` with not Match([Type], 'FO'),   data loads but rows with FO are still there 

LIB CONNECT TO [C--Users-FackT-Desktop-DOWNLOAD-A - Bijlages-Rapportage-Databases-REP01 & REP02-REP01 - PO data.accdb];

[qry_REP01_02_AR]:

Load

`Agmt`,

`Auto/Man`,

`Buyer`,

`Buying Channel`,

`Category 2`,

`Category description`,

`Cluster`,

`Company Name`,

`Euro price`,

`Euro value`,

`Item`,

`Item category`,

`Material Group descr`,

`Material Type`,

`MaterialNr`,

`Matl Group`,

`Name`,

`OPU`,

`OUN`,

`PC L1`,

`PC L2`,

`Per`,

`PGr`,

`PGr Scope`,

`Plnt`,

`Plnt description`,

`PO Quantity`,

`PurchDoc`,

  If(Len(Region) = 0, 'NA',Region) as Region,

`Short Text`,

`Type`,

`Type of vendor`,

`Vendor`,

`Year/Month`;

SQL SELECT

`Agmt`,

`Auto/Man`,

`Buyer`,

`Buying Channel`,

`Category 2`,

`Category description`,

`Cluster`,

`Company Name`,

`Euro price`,

`Euro value`,

`Item`,

`Item category`,

`Material Group descr`,

`Material Type`,

`MaterialNr`,

`Matl Group`,

`Name`,

`OPU`,

`OUN`,

`PC L1`,

`PC L2`,

`Per`,

`PGr`,

`PGr Scope`,

`Plnt`,

`Plnt description`,

`PO Quantity`,

`PurchDoc`,

`Region`,

`Short Text`,

`Type`,

`Type of vendor`,

`Vendor`,

`Year/Month`

FROM `qry_REP01_02_AR`;

Tags (2)
1 Solution

Accepted Solutions
undergrinder
Valued Contributor II

Re: Exclude rows by value from load

Hi Tom,

the where condition must be after from statement.

So:

Load

     Column_1,

     Column_2,

     Column_n,

From

Where Type <> 'FO'


G.

7 Replies
alexdataiq
Contributor III

Re: Exclude rows by value from load

What error appears when you use Where Type <> 'FO' ?

Cheers

okolyug16
Contributor II

Re: Exclude rows by value from load

Hi Tom,

Try this:

WHERE NOT WildMatch(Type, '*FO*')

Example:

Capture.PNG

tomtastic
New Contributor II

Re: Exclude rows by value from load

Hi Alejandro,

See below:

SeeError where.jpg

tomtastic
New Contributor II

Re: Exclude rows by value from load

Hi okolyug16,

I tried that but get the following error. Similar as to what I was experiencing with Where Type <> 'FO'

WildMatch attempt.jpg

undergrinder
Valued Contributor II

Re: Exclude rows by value from load

Hi Tom,

the where condition must be after from statement.

So:

Load

     Column_1,

     Column_2,

     Column_n,

From

Where Type <> 'FO'


G.

brunobertels
Valued Contributor

Re: Exclude rows by value from load

Hi

As undergrider said you must put your where clause after statement :

lib connect to ....

LOAD

.....

.....

FROM

Where Type <> 'FO';


OR


WHERE NOT WildMatch(Type, '*FO*');

tomtastic
New Contributor II

Re: Exclude rows by value from load

Hi Undergrinder,

That did the trick. Thanks for the right answer.

Community Browser