topic Re: Where Statement Syntax in QlikView

Where Statement Syntax

Anonymous — Sat, 19 Sep 2015 23:49:34 GMT

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'

Re: Where Statement Syntax

MarcoWedel — Sun, 20 Sep 2015 00:46:41 GMT

Yes, you can!

Re: Where Statement Syntax

Anonymous — Mon, 21 Sep 2015 21:29:04 GMT

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?

Re: Where Statement Syntax

MarcoWedel — Mon, 21 Sep 2015 21:35:25 GMT

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

Re: Where Statement Syntax

Anonymous — Mon, 21 Sep 2015 21:47:00 GMT

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

Re: Where Statement Syntax

MarcoWedel — Mon, 21 Sep 2015 22:11:15 GMT

please post sample data and expected result.

Re: Where Statement Syntax

Anonymous — Mon, 21 Sep 2015 23:13:33 GMT

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

Re: Where Statement Syntax

MarcoWedel — Mon, 21 Sep 2015 23:37:02 GMT

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

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

hope this helps

regards

Marco

Re: Where Statement Syntax

Anonymous — Tue, 22 Sep 2015 00:19:58 GMT

Thanks

Re: Where Statement Syntax

MarcoWedel — Tue, 22 Sep 2015 05:42:05 GMT

You're welcome

Regards

Marco