topic Re: Flag field, NOT MATCH not functioning properly in QlikView

Flag field, NOT MATCH not functioning properly

jchambers123 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm trying to create a flag field, but am getting some unexpected results. The field, which I'll call 'FieldX', has values from 1 to 1000. I want to flag 2 of those values in particular (0794 and 0795). That is, I want the flag field to indicate '1' if the values are 0794 or 0795, and '0' otherwise. The script I wrote is below.

NoConcatenate Temp:

Load *,

if(Not Match(FieldX,0794,0795),1,0) AS FLAG1,

...

Resident Table1; Drop Table1

Rename Table Temp to Table 2;

The problem is that its returning 0's for the values it is supposed to (i.e., 0794 and 0795) but also many others it shouldn't (e.g., 267, 9443). I tried enclosing the values in apostrophes to see if that made any difference, and it didn't.

Any ideas what I've done wrong? Or suggested corrections?

(Also, I'm wondering if part of the issue is a numeric vs. string data difference and perhaps the 0 prefixes for 0-999 values may be creating problems)?

Re: Flag field, NOT MATCH not functioning properly

sunny_talwar — Tue, 29 Aug 2017 19:31:42 GMT

May be try this

If(Not Match(Text(FieldX), '0794', '0795'),1,0) as FLAG1,

Re: Flag field, NOT MATCH not functioning properly

Clever_Anjos — Tue, 29 Aug 2017 19:41:30 GMT

Try using numbers instead of strings

Load *,

if(Not Match(FieldX+0,794,795),1,0) AS FLAG1,

Re: Flag field, NOT MATCH not functioning properly

jchambers123 — Tue, 29 Aug 2017 19:59:01 GMT

Thanks, Sunny, but I tried adding the text function as you suggested and its still returning incorrect results-- in fact, now 0794/0795 are returning 0's instead of 1's (they should be 1's), strangely enough.

Re: Flag field, NOT MATCH not functioning properly

effinty2112 — Tue, 29 Aug 2017 20:03:37 GMT

Hi John,

Maybe:

sign(Match(num(FieldX),794,795)) AS FLAG1

Cheers

Andrew

Re: Flag field, NOT MATCH not functioning properly

sasiparupudi1 — Tue, 29 Aug 2017 20:37:32 GMT

May be try

if(Not Match(Num(FieldX),794,795),1,0) AS FLAG1,

Re: Flag field, NOT MATCH not functioning properly

jchambers123 — Wed, 30 Aug 2017 13:00:01 GMT

Thanks for your suggestions, Andrew, Clever and Sasidhar. I tried them all but unfortunately its still not returning the correct values. Namely, values other than 0794/0795 are still returning as true (i.e., 1's) rather than false

Re: Flag field, NOT MATCH not functioning properly

sasiparupudi1 — Wed, 30 Aug 2017 13:05:40 GMT

May be reverse the condition

if(Match(Num(FieldX),794,795),1,0) AS FLAG1

Re: Flag field, NOT MATCH not functioning properly

jchambers123 — Wed, 30 Aug 2017 13:14:56 GMT

Hi Sasidhar-- just tried that. But no luck-- same problem. Thanks though.

Re: Flag field, NOT MATCH not functioning properly

Anil_Babu_Samineni — Wed, 30 Aug 2017 13:18:40 GMT

May be share some real values from your source.

Re: Flag field, NOT MATCH not functioning properly

sunny_talwar — Wed, 30 Aug 2017 13:28:42 GMT

Would you be able to share a sample to look at?

Re: Flag field, NOT MATCH not functioning properly

sunny_talwar — Wed, 30 Aug 2017 13:29:34 GMT

How about this

If(Not WildMatch(FieldX, '*794*', '*795*'), 1, 0) as FLAG1,

Re: Flag field, NOT MATCH not functioning properly

jchambers123 — Wed, 30 Aug 2017 14:43:09 GMT

Thanks all. I may have discovered the source of the problem. The field in question had been linked to a dimensions table that had some out-of-date values (the values which the flags had not been picking up). When I decoupled the field from that table, those values disappeared and the flags now function properly. Not sure why or how it had that effect, but at least that's my guess... In any event, I appreciate all of you weighing in. Best, -John