topic Re: Exclude rows by value from load in App Development

Exclude rows by value from load

Anonymous — Thu, 02 Jun 2016 19:51:59 GMT

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`;

Re: Exclude rows by value from load

alexdataiq — Thu, 02 Jun 2016 20:15:24 GMT

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

Cheers

Re: Exclude rows by value from load

Anonymous — Thu, 02 Jun 2016 21:46:42 GMT

Hi Tom,

Try this:

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

Example:

Re: Exclude rows by value from load

Anonymous — Fri, 03 Jun 2016 08:29:25 GMT

Hi Alejandro,

See below:

See

Re: Exclude rows by value from load

Anonymous — Fri, 03 Jun 2016 08:30:48 GMT

Hi okolyug16,

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

Re: Exclude rows by value from load

undergrinder — Fri, 03 Jun 2016 08:48:49 GMT

Hi Tom,

the where condition must be after from statement.

So:

Load

Column_1,

Column_2,

Column_n,

From

Where Type <> 'FO'

Re: Exclude rows by value from load

brunobertels — Fri, 03 Jun 2016 08:57:08 GMT

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

lib connect to ....

LOAD

.....

FROM

Where Type <> 'FO';

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

Re: Exclude rows by value from load

Anonymous — Fri, 03 Jun 2016 09:19:10 GMT

Hi Undergrinder,

That did the trick. Thanks for the right answer.