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