7 Replies Latest reply: Jun 3, 2016 5:19 AM by Tom Fack RSS

    Exclude rows by value from load

    Tom Fack

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