topic Re: Null Value with Match/WildMatch and Like in QlikView

Null Value with Match/WildMatch and Like

sunny_talwar — Mon, 19 Oct 2015 01:20:36 GMT

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

];

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.

Re: Null Value with Match/WildMatch and Like

settu_periasamy — Mon, 19 Oct 2015 02:52:03 GMT

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

Re: Null Value with Match/WildMatch and Like

jonathandienst — Mon, 19 Oct 2015 05:20:10 GMT

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.

Re: Null Value with Match/WildMatch and Like

sunny_talwar — Mon, 19 Oct 2015 10:34:44 GMT

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?

Re: Null Value with Match/WildMatch and Like

jonathandienst — Mon, 19 Oct 2015 11:37:02 GMT

How about this?

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

Re: Null Value with Match/WildMatch and Like

sunny_talwar — Mon, 19 Oct 2015 12:01:30 GMT

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?

Re: Null Value with Match/WildMatch and Like

jonathandienst — Mon, 19 Oct 2015 12:38:33 GMT

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.

Re: Null Value with Match/WildMatch and Like

sunny_talwar — Mon, 19 Oct 2015 12:46:30 GMT

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

Re: Null Value with Match/WildMatch and Like

narband2778 — Fri, 19 Jan 2018 11:21:33 GMT

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'

Re: Null Value with Match/WildMatch and Like

narband2778 — Fri, 19 Jan 2018 15:32:40 GMT

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

Re: Null Value with Match/WildMatch and Like

Digvijay_Singh — Wed, 18 Aug 2021 17:34:40 GMT

Thanks @sunny_talwar !!!

I had this issue since long, somehow I was managing using too many individual conditions like A <> 'ABC' and A <> 'BCA' and A <> 'DCA' but the list was growing so I had to somehow make match function work along with inclusion of nulls.

Your post help me to manage it using isnull(fieldname) along with match.

Thanks!!

Digvijay