topic Re: NOT MATCH Function Acting Weird in QlikView

NOT MATCH Function Acting Weird

alexandergrando — Mon, 05 Jun 2017 17:15:28 GMT

Hi,

I am using a NOT MATCH function to filter out certain asset types:

If(NOT MATCH(AssetType, 'Garage', 'Retail', 'HOA'), 'Yes') as Included

The weird thing is that the function is also partially excluding those records with a blank or NULL value as AssetType. But it is not excluding all of them, only about half. Another weird thing is that if I replace AssetType with 'MISSING' instead of blank/NULL earlier in the script then it works fine:

If(Len(AssetType) = 0, 'MISSING', AssetType) as AssetType

Can anyone shed some light on why the NOT MATCH function is behaving inconsistently when it comes to blank or NULL values, please?

Re: NOT MATCH Function Acting Weird

m_woolf — Mon, 05 Jun 2017 17:20:06 GMT

It sounds like you have some values that are not NULL but have a length of 0.

Re: NOT MATCH Function Acting Weird

sunny_talwar — Mon, 05 Jun 2017 17:22:18 GMT

Try using Like instead of Match

If(Not AssetType Like 'Garage' and

Not AssetType Like 'Retail'

Not AssetType Like 'HOA', 'Yes') as Included

Re: NOT MATCH Function Acting Weird

alexandergrando — Mon, 05 Jun 2017 17:22:27 GMT

I see. But why is the NOT MATCH function affecting those rows at all?

Re: NOT MATCH Function Acting Weird

sunny_talwar — Mon, 05 Jun 2017 17:24:47 GMT

Also look here for similar discussion

Null Value with Match/WildMatch and Like