Null Value with Match/WildMatch and Like

[sunny_talwar]

Hi Everyone,

I am trying to understand if this is a bug or if this is an expected behavior. Let's look at the following script:

Table:

LOAD *,

  If(Match(Value, 50) or Len(Trim(Value)) = 0, 1, 0) as Flag1,

  If(not Match(Value, 60), 1, 0) as Flag2,

  If(not Value like 60, 1, 0) as Flag3,

  If(not RangeSum(Match(Value, 60)), 1, 0) as Flag4;

LOAD Dim,

  If(Len(Trim(Value)) > 0, Value) as Value

Inline [

Dim, Value

A, 50

B, 60

C

];

I would expect that all the flags should give me the same value as I am just writing the same thing in many different ways (60 should be 0 and 50 and null should be flagged as 1). But Flag2 doesn't really behave like that. For some reason Match puts 0 into the null bucket as well. Is this a usual behavior or is this a bug????

I used to prefer using Match/WildMatch functions over using like function, but it seems that like is doing what I intended to do with the Match.

I did find a work around suggested by swuehl‌ using the RangeSum() function around the match function. But as I mentioned, I am just trying to understand Match/WildMatch behaivor here.

Thanks in advance for all the responses.

Best,

Sunny

Message was edited by: Sunny Talwar Adding the sample qvw with this post.

[settu_periasamy]

Hi Sunny,

Not sure. But, I think it is based on the Null Value.

not Match(Value, 60) value will return the '-' Value.  Not '0' or '-1'.

So, it will go to the false expression. I checked it in Straight table (with Show All Value, and uncheck the Suppress Zero Values, it gives extra row for (Dim1).

Regards,

Settu P

[jonathandienst]

Sunny - I don't think the behaviour of Flag2 is a bug.

Match(Value, 60) will return a null if Value is null, and any comparison involving a null value will return false; and the not is a part of the comparison that returns false.

For example: =If(Not Null(), 1, 0) returns 0.

[sunny_talwar]

So then looking at Flag 3 -> If(not Value like 60, 1, 0) as Flag3,

How is this able to consider null and give it the right flag?

[jonathandienst]

How about this?

If(((not Value like 60) Or IsNull(Value)), 1, 0) as Flag3,

[sunny_talwar]

Hi jonathan dienst‌,

This will work. I am just trying to understand why not Match ignores null, but not Field like is able to detect it?

[jonathandienst]

This is the way I see it:

"Field like ..." is a comparison already, which will return true or false and will never return Null() -- so Not can invert its state.

"Match(Value, ...)" is not a comparison, so it can return Null() if Value is null. Not Null() always evaluates to false.

[sunny_talwar]

I understand what you are saying. Actually I was using this in the where statement (Where not Match(FieldName, 'A', 'B','C');) and found out that not only did A, B, C was removed but also the null value. Since I only wanted to exclude A, B and C which I explicitly use in my match function I was hoping Null would go in.

I guess its better to use like in this case or add IsNull(FieldName) to the where clause.

Thanks guys. Really appreciate your responses.

Best,

Sunny

[narband2778]

Hi Sunny,

You are right. Even MS SQL behaves the same with NOT IN or Not Equals to when I use in Where Clause.

ISNULL(AP.SiteCode, 'NULL') <> 'NZB001'

[narband2778]

Hi Sunny,

i am trying to exclude below list values from where clause

NOT MATCH([FK_TREATMENT_FUNCTION], '560','610','700','701','702','703','704','705','706','707','708','709', '710','711','712','712','714','715') but this is excluding NULL values as well.

Can you please look into this and let me know, what I am missing. As, I want to include the NULL values.

Thanks,

Naresh