topic Re: Match Function is excluding null values in QlikView

Match Function is excluding null values

Wed, 24 Jun 2015 14:00:39 GMT

Im using the Match function to exclude a particular value however it also seems to be removing null values . Does anyone have any ideas ?

Re: Match Function is excluding null values

swuehl — Wed, 24 Jun 2015 14:03:32 GMT

Could you post your expression / statement and some sample data. And what you expect to get?

Re: Match Function is excluding null values

Anonymous — Wed, 24 Jun 2015 14:04:48 GMT

How about something like

If ( isnull[[YourField] or match(........) , .....................

Re: Match Function is excluding null values

Anonymous — Wed, 24 Jun 2015 14:06:43 GMT

Can we use below expression

if(not(isnull([Field])), Match(...))

Re: Match Function is excluding null values

Wed, 24 Jun 2015 14:18:01 GMT

Sorry my expression is

WHERE (FieldName = 'CountryStatus'

AND Match(FieldValue, 'Inactive) = 0)

;

this removes all Country statues which are 'inactive'. However it also removes all records that don't have a CountryStatus assigned ie nulls which is not what I want.

Re: Match Function is excluding null values

Anonymous — Wed, 24 Jun 2015 14:30:32 GMT

WHERE (FieldName = 'CountryStatus'

AND

( Match(FieldValue, 'Inactive') = 0 or isnull(FieldValue) )

Re: Match Function is excluding null values

swuehl — Wed, 24 Jun 2015 14:31:09 GMT

Seems to work for me:

LOAD *

WHERE FieldName = 'CountryStatus' and not FieldValue = 'Inactive';

LOAD recno() as ID, FieldName, if(len(trim(FieldValue)),FieldValue, NULL()) as FieldValue INLINE [

FieldName, FieldValue

CountryStatus, Active

CountryStatus, Inactive

CountryStatus,

];

Re: Match Function is excluding null values

jonas_rezende — Wed, 24 Jun 2015 14:44:33 GMT

Hi.

When Country Status is null, which is value of FieldValue?

Re: Match Function is excluding null values

Wed, 24 Jun 2015 14:52:17 GMT

Hi Bill I tried this however it returns values with CountryStatus as Inactive

Re: Match Function is excluding null values

swuehl — Wed, 24 Jun 2015 15:01:54 GMT

Or if you want to stick to Match():

WHERE FieldName = 'CountryStatus' AND Rangesum(Match(FieldValue,'Inactive'))=0;

Re: Match Function is excluding null values

Wed, 24 Jun 2015 17:03:15 GMT

Thanks yes this solution worked