Qlik Community

Qlik Sense App Development

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

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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.

View solution in original post

7 Replies
Partner
Partner

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

Highlighted
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.

View solution in original post

brunobertels
Valued Contributor II

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.