Exclude multiple values in a field using load script where clause in qliksense (Data source: Hive)

[adarshiyerck]

Hello Everyone,

I am stuck with a situation where I need to exclude multiple values in load script from a field in Qliksense. I have tried following - 

Query 1:

SQL SELECT *
FROM HIVE.DBName.Table Name where [Field] <> 'value1' or [Field] <> 'value2' or [Field] <> 'value3'; I can still see the values after load

Query 2:
SQL SELECT *
FROM HIVE.DBName.Table Name where [Field] != 'value1' or [Field] != 'value2' or [Field] != 'value3';

I can still see the values after load

Query 3:
SQL SELECT *
FROM HIVE.DBName.Table Name where [Field] != 'value1';

Works if I use only 1 value

Query 4:

SQL SELECT *
FROM HIVE.DBName.TableName  Where not  match([Field],'Value1','Value2','Value3');

error syntax match not found

Any suggestions here or workaround how this can be achieved?

[Vegar · Accepted solution]

You need to use SQL query syntax in a SQL SELECT query. 

Try this:

SQL SELECT *
FROM

HIVE.DBName.TableName 

WHERE Field NOT IN ('Value1','Value2','Value3');

[adarshiyerck]

Hello Vegar,

This solution works, can I extend this condition by adding 'and' date>=20190101; further?

[Pirolli · Accepted solution]

Vegar's reply is best; if you want to keep the syntax in Qlik language you will need to use the Query4 syntax (or your syntax from Query 1):

Load *

Where not match(field,'value1','value2');

SQL SELECT *
FROM HIVE.DBName.Table;

This will use a preceding load from the HIVE database; although all rows will be returned to Qlik from the HIVE database. Again, Vegar's response to inject the where clause using native database language is best.

[adarshiyerck]

Hello Pirolli,

Yes this helps too. Thank you.

[adarshiyerck]

Any thoughts on this

nesting another condition in where clause after excluding values filter using date field