Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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
Yes, you can!
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?
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);
its still removing any row that meets any one of the criteria as opposed to all criteria.
please post sample data and expected result.
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
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
Thanks
You're welcome
Regards
Marco