topic Re: How to exclude data based on spreadsheet value in QlikView

How to exclude data based on spreadsheet value

Mon, 13 Aug 2012 02:48:14 GMT

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$);

Re: How to exclude data based on spreadsheet value

Anonymous — Mon, 13 Aug 2012 04:13:15 GMT

One way.......create an inline table at the beginning of your script that contains the column name TestType and the keywords you want to exclude (this could also be another spreadsheet or database table if you want users to maintian the list of keywords). Then in your existing script, use "Where Not Exists(TestType)" to exclude data matching the keywords you loaded.

Re: How to exclude data based on spreadsheet value

Mon, 13 Aug 2012 21:24:47 GMT

Sorry, Im not very skilled with Qlikview code, and not sure at all about inline tables.

Is there not as easier piece of script to use that I can add under the code that's already there to only show data when the "TestType" field is blank? Or when there is certain words in it?

Similar to how the "Where Year > 2010" works? I've tried changing this around already though but can't figure it out.

Re: How to exclude data based on spreadsheet value

Anonymous — Mon, 13 Aug 2012 21:39:29 GMT

Try this:

Directory;

LOAD Batch,
     ReleaseDate,
     Comments,
     TestType,
     DateReceived as SampleDate

FROM [...\Desktop\Lab Samples New - From 01JAN11 - Test.xls]
(biff, embedded labels, table is ReleaseData$)

Where IsNull(TestType) or Not Match(TestType,'Retest','Stability');

Re: How to exclude data based on spreadsheet value

Mon, 13 Aug 2012 22:46:32 GMT

Thank you so much. This appears to work perfectly.