Exclude rows by value from load

[Anonymous]

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

[alexdataiq]

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

Cheers

[Anonymous]

Hi Tom,

Try this:

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

Example:

[Anonymous]

Hi Alejandro,

See below:

See

[Anonymous]

Hi okolyug16,

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

[undergrinder · Accepted solution]

Hi Tom,

the where condition must be after from statement.

So:

Load

     Column_1,

     Column_2,

     Column_n,

From

Where Type <> 'FO'

G.

[brunobertels]

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*');

[Anonymous]

Hi Undergrinder,

That did the trick. Thanks for the right answer.