topic Re: Exclude multiple values in a field using load script where clause in qliksense (Data source: Hiv in App Development

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

adarshiyerck — Fri, 13 Sep 2019 16:29:32 GMT

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?

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

Vegar — Fri, 13 Sep 2019 16:34:47 GMT

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

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

adarshiyerck — Fri, 13 Sep 2019 17:03:33 GMT

Hello Vegar,

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

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

Pirolli — Fri, 13 Sep 2019 17:05:49 GMT

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.

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

adarshiyerck — Fri, 13 Sep 2019 17:11:51 GMT

Hello Pirolli,

Yes this helps too. Thank you.

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

adarshiyerck — Fri, 13 Sep 2019 17:16:03 GMT

Any thoughts on this

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