Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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?
How about this?
If(((not Value like 60) Or IsNull(Value)), 1, 0) as Flag3,
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?
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.
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
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'
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