Have you a trick for : Field not like list of value
Moderators: chulett, rschirm, roy
Have you a trick for : Field not like list of value
Hi there,
I'd like to know if someone would have a trick to check if a field does not contain any word in a list of words.
Something doing the check :
If Index(ds1.field,"word01",1) + Index(ds1.field,"word02",1) + Index(ds1.field,"word03",1) + ... + Index(ds1.field,"word99",1) = 0 Then 0 Else 1
without having to write 99 words.
Thanks.
I'd like to know if someone would have a trick to check if a field does not contain any word in a list of words.
Something doing the check :
If Index(ds1.field,"word01",1) + Index(ds1.field,"word02",1) + Index(ds1.field,"word03",1) + ... + Index(ds1.field,"word99",1) = 0 Then 0 Else 1
without having to write 99 words.
Thanks.
Use
It is more efficient as well.
Code: Select all
Index("Word01,Word02,Word03,Word04,",ds1.field:',')
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi ArndW,
I think this is not doing the same thing because I don't want to check if my field is equal to any of the unwanted word but I want to check if my field contains any of those words ... This is the "contains" part which causes me trouble.
It's the same pain in SQL when you have to code
WHERE Field not like '%Word01%' and Field not like '%Word02%' ... Field not like '%Word09%'.
I think this is not doing the same thing because I don't want to check if my field is equal to any of the unwanted word but I want to check if my field contains any of those words ... This is the "contains" part which causes me trouble.
It's the same pain in SQL when you have to code
WHERE Field not like '%Word01%' and Field not like '%Word02%' ... Field not like '%Word09%'.
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
If you can do this in UNIX or Oracle (Oracle supports regular expression), pattern matching may help.
Or you could call shell commands in your job somehow, e.g. echo yourfield | grep -c -e "word1|word2..."
Or you could write a parallel routine to achieve this.
Or you could call shell commands in your job somehow, e.g. echo yourfield | grep -c -e "word1|word2..."
Or you could write a parallel routine to achieve this.
renaudfa wrote:Hi ArndW,
I think this is not doing the same thing because I don't want to check if my field is equal to any of the unwanted word but I want to check if my field contains any of those words ... This is the "contains" part which causes me trouble.
It's the same pain in SQL when you have to code
WHERE Field not like '%Word01%' and Field not like '%Word02%' ... Field not like '%Word09%'.
Edward Yuan
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
Or if you can afford the cost of Basic Transformer, the server built-in routine MatchField does the trick:)
Yuan_Edward wrote:If you can do this in UNIX or Oracle (Oracle supports regular expression), pattern matching may help.
Or you could call shell commands in your job somehow, e.g. echo yourfield | grep -c -e "word1|word2..."
Or you could write a parallel routine to achieve this.
renaudfa wrote:Hi ArndW,
I think this is not doing the same thing because I don't want to check if my field is equal to any of the unwanted word but I want to check if my field contains any of those words ... This is the "contains" part which causes me trouble.
It's the same pain in SQL when you have to code
WHERE Field not like '%Word01%' and Field not like '%Word02%' ... Field not like '%Word09%'.
Edward Yuan
Seems to me the only way to avoid having to type in and match 99 'words' each time would be to load them once into some kind of lookup structure. Then parse your source down and throw each one against that lookup to see if you get any hits. Other than something like that, I don't see how you do it "without having to write 99 words".
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Index("Word01,Word02,Word03,Word04,",ds1.field:','
Hi, in regards to
Index("Word01,Word02,Word03,Word04,",ds1.field:',')
I cannot get this to work. I use the same format, but get a transformr error(not enough arguments to function index). Was wondering if someone else was able to get a flavor of this code to work?
Here is what my code looks like.
Index("Agency,Agents,Bank,Inc,",input.field:',')
Index("Word01,Word02,Word03,Word04,",ds1.field:',')
I cannot get this to work. I use the same format, but get a transformr error(not enough arguments to function index). Was wondering if someone else was able to get a flavor of this code to work?
Here is what my code looks like.
Index("Agency,Agents,Bank,Inc,",input.field:',')
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Yuan,Yuan_Edward wrote:If you can do this in UNIX or Oracle (Oracle supports regular expression), pattern matching may help.
Or you could call shell commands in your job somehow, e.g. echo yourfield | grep -c -e "word1|word2..."
Or you could write a parallel routine to achieve this.
renaudfa wrote:Hi ArndW,
I think this is not doing the same thing because I don't want to check if my field is equal to any of the unwanted word but I want to check if my field contains any of those words ... This is the "contains" part which causes me trouble.
It's the same pain in SQL when you have to code
WHERE Field not like '%Word01%' and Field not like '%Word02%' ... Field not like '%Word09%'.
DataStage too supports regular expressions.
Read the orchestrate manual and look for filter stage details.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
Index("Word01,Word02,Word03,Word04,",ds1.field:','
Hi everyone, thanks for reading and your suggestions. If I could get this code posted by Arndw to work; it may be all I need.
Arndw's version - Index("Word01,Word02,Word03,Word04,",ds1.field:',')
my version - Index("Agency,Agents,Bank,Inc,",input.field:',')
I get an error asking for more arguments. I not sure what I'm missing. Where does the instance or occurance go? I have never used this format for the Index statement.
I can get this code below to compile and run with no errors, but it will not occomplish what I need. This code below matches what's in the manual. I would rather get the code above working so I can search an input field for lots of strings. Also I would not have to code a routine or server shared container.
Index(DSLink83.FULL_NAME,"ASSOCIATES,SERVICES,SERVICE,MANAGEMENT", 1)
Thanks!!!
Arndw's version - Index("Word01,Word02,Word03,Word04,",ds1.field:',')
my version - Index("Agency,Agents,Bank,Inc,",input.field:',')
I get an error asking for more arguments. I not sure what I'm missing. Where does the instance or occurance go? I have never used this format for the Index statement.
I can get this code below to compile and run with no errors, but it will not occomplish what I need. This code below matches what's in the manual. I would rather get the code above working so I can search an input field for lots of strings. Also I would not have to code a routine or server shared container.
Index(DSLink83.FULL_NAME,"ASSOCIATES,SERVICES,SERVICE,MANAGEMENT", 1)
Thanks!!!
Well, I told you what was missing to allow the function to compile and run, never said it would accomplish what you needed.
Perhaps this would be more appropriate...
You'd obviously need to do the convert outside this function as it only needs to happen once, not over and over on every record.
Perhaps this would be more appropriate...
Code: Select all
Index(Convert(",",@FM,"Word01,Word02,Word03,Word04"),ds1.field,1)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Index("Word01,Word02,Word03,Word04,",ds1.field
Since I neglected the last parameter it won't compile and run, as Craig has posted.RStone wrote:If I could get this code posted by Arndw to work...
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Did you consider using a lookup_int16_from_string() function in a Modify stage? There is no equivalent in a Transformer stage. The table definition in the function can include a default value - I suggest 0.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.