Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
sunny_talwar

Null Value with Match/WildMatch and Like

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

];


Capture.PNG

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.

10 Replies
settu_periasamy
Master III
Master III

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).

Capture.JPG

Regards,

Settu P

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar
Author

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
Partner - Champion III
Partner - Champion III

How about this?

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar
Author

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar
Author

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
Creator II
Creator II

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
Creator II
Creator II

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