Where Statement Syntax

[Anonymous]

I want to exclude data from my data load.  I know I can use as example: Where field <> 'name'.

What I would like to do is exclude data when two different fields have specific names.  Can I use And?  Is this the correct syntax?

Where field1 <> 'name1' and field2 <> 'name2'

[MarcoWedel]

Yes, you can!

[Anonymous]

I attempted to exclude data load (loading from a QVD file) using Where field1 <> 'name1' and field2 <> 'name2' but this did not work.  It essentially removed all the data.  I'm looking to remove specific data from the following table:

LOAD

     Market,

     Parts,

     Scenario,

     Quantity,

     [Quantity - Pounds],

     [Quantity - Kilograms],

     [Quantity - Grams],

     [Price per Unit],

     [Average PPU],

     [Average PPU-LB],

     [Average PPU-KG],

     [Average PPU-GR],

     Spend,

     [Purchase Price Variance],

     Quarter,   

     Month,

     Currency,

     Right(Year,4) as Year,

     Variance,

     Month_Year,

     Comment,

     Market_Group  

I want to exclude where any row has the following:

Field          Name

Month           Jul

Scenario     Actual

Year          2015

Market     Null()

Thoughts?

[MarcoWedel]

LOAD

     Market,

     Parts,

     Scenario,

     Quantity,

     [Quantity - Pounds],

     [Quantity - Kilograms],

     [Quantity - Grams],

     [Price per Unit],

     [Average PPU],

     [Average PPU-LB],

     [Average PPU-KG],

     [Average PPU-GR],

     Spend,

     [Purchase Price Variance],

     Quarter,  

     Month,

     Currency,

     Right(Year,4) as Year,

     Variance,

     Month_Year,

     Comment,

     Market_Group 

Where  Month <> 'Jul' and  Scenario <> 'Actual' and Year <> 2015 and Len(Market);

[Anonymous]

its still removing any row that meets any one of the criteria as opposed to all criteria.

[MarcoWedel]

please post sample data and expected result.

[Anonymous]

I created an example set of data in the attached QVD file.  There are five fields labeled field1,field2,etc.

The challenge is to load all the data into a QV file except where rows have field1 = Jul, field2 = 2015 and field3 = Actual

[MarcoWedel · Accepted solution]

Hi,

solutions could be:

LOAD field1, 
     field2, 
     field3, 
     field4, 
     field5
FROM [https://community.qlik.com/servlet/JiveServlet/download/871070-186097/Test_Exclusion.qvd] (qvd)
Where field1 <> 'Jul' or field2 <> 2015 or field3 <> 'Actual';

or

Where not (field1 = 'Jul' and field2 = 2015 and field3 = 'Actual');

hope this helps

regards

Marco

[Anonymous]

Thanks

[MarcoWedel]

You're welcome

Regards

Marco