4 Replies Latest reply: Aug 13, 2012 6:46 PM by John Reardon RSS

    How to exclude data based on spreadsheet value

      Hey, thought I made this discussion earlier but it doesn't appear to have saved.

       

      I have a spreadsheet with test results and release dates which pulls through into QV. I want QV to exclude any data that has certain words in the "TestType" column, eg. Retest, Stability etc.

       

      I already use a "where" function to only show data after year 2010. But I can't figure out how to make it work for this situation.

      Here is a copy of the current script.

       

      What code would I use to exclude data based on what in in the TestType field?

       

      //Batches

       

      LOAD Date(syscreated,'DD/MM/YYYY') as ReceiptDate,
         
      Date(MonthStart(syscreated),'MMM-YY') as MonthReceipted,
         
      text(Date(MonthStart(syscreated),'MMM')) as MthReceipted,
         
      Date(YearStart(syscreated),'YYYY') as YearReceipted,
         
      ItemCode as ReceiptCode,
         
      Number as Batch,
         
      pick( match(left(ItemCode,1),'1','F','Z','W','Y','V','X'),'Raw Materials','Finished Products','Labels','Cartons','Bottles & Lids','Foils','Bulk') as ProductGroup,
         
      syscreated as Receipted
         
      Where Year(syscreated) > '2010';SQL SELECT
           ItemCode,
           Number,
          syscreated
      FROM "009".dbo.ItemNumbers;

       

      Directory;

      LOAD Batch,
          
      ReleaseDate,
          
      Comments,
          
      TestType,
          
      DateReceived as SampleDate FROM
      [...\Desktop\Lab Samples New - From 01JAN11 - Test.xls]
      (
      biff, embedded labels, table is ReleaseData$);