NOT MATCH Function Acting Weird

[alexandergrando]

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?

[m_woolf]

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

[sunny_talwar]

Try using Like instead of Match

If(Not AssetType Like 'Garage' and

   Not AssetType Like 'Retail'

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

[alexandergrando]

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

[sunny_talwar · Accepted solution]

Also look here for similar discussion

Null Value with Match/WildMatch and Like