Flag field, NOT MATCH not functioning properly

[jchambers123]

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

[sunny_talwar]

May be try this

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

[Clever_Anjos]

Try using numbers instead of strings

Load *,

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

[jchambers123]

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.

[effinty2112]

Hi John,

Maybe:

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

Cheers

Andrew

[sasiparupudi1]

May be try

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

[jchambers123]

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

[sasiparupudi1]

May be reverse the condition

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

[jchambers123]

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

[Anil_Babu_Samineni]

May be share some real values from your source.