Page 1 of 1
Transformer constraints with multiple values?
Posted: Thu Jan 29, 2009 10:48 am
by PilotBaha
Is there a way to figure out if a field is equal to a value in a set using transformer constraints?
What I am trying to do is to avoid doing
Code: Select all
Link.Field = 1 or
Link.Field = 2 or
Link.Field = 3
but write something like
I couldn't find a way to do this in the constraints pull downs in the transformer..
Posted: Thu Jan 29, 2009 11:34 am
by throbinson
INDEX('123',Link.Field,1)
Posted: Thu Jan 29, 2009 11:57 am
by kandyshandy
this won't work. He wants to see whether 1 or 2 or 3 is the value...
I don't know what PilotBaha is trying to achieve...
want to save some time? or want to do a copy paste from SQL in DS transformer constraints?
Try CONVERT('123', '', Field) =''
Posted: Thu Jan 29, 2009 2:12 pm
by PilotBaha
The second poster is correct. Index won't work.. I am trying to avoid 10 different if statements with 9999 different indentations
Posted: Thu Jan 29, 2009 2:22 pm
by throbinson
hunh? I just tried it. works great. What doesn't work about it? If 1 or 2 or 3 is in LINK.FIELD the result will be non-zero.
Posted: Thu Jan 29, 2009 2:24 pm
by kandyshandy
PilotBaha, Just to remind you... CONVERT works on one character at a time. So be cautious, if you want to check for anything more than 1 character.
Posted: Thu Jan 29, 2009 2:28 pm
by throbinson
And yet Index will work on more then one character. Advantage -- Index!
Posted: Thu Jan 29, 2009 2:46 pm
by DSguru2B
INDEX will work. I have used list iterations like this one before and it works great.
Posted: Thu Jan 29, 2009 2:51 pm
by throbinson
Boom! I think I originally got it from a DSGuruB post.
Posted: Thu Jan 29, 2009 3:28 pm
by ray.wurlod
Sorry I'm late to this.
Index() WILL work, at least for single-character values in Link.Field1.
Further, it will be more efficient than any of the other methods postulated.
Posted: Thu Jan 29, 2009 3:50 pm
by throbinson
I've used it for two digit codes. The trick was inserting a character that wouldn't appear in the field like this;
INDEX('12|23|45|XY',LINK.FIELD,1). You'll get a non-zero return for 12, 23, 45, and XY codes. You can go bigger I'm sure, you just have to make sure that a field doesn't show up as a subset of one of the other choices within the "delimiter".
Posted: Thu Jan 29, 2009 5:41 pm
by ray.wurlod
You may need to be careful of data types in parallel jobs. Otherwise Index() is the best tool for the job specified in the original request.