Have you a trick for : Field not like list of value

[renaudfa]

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.

[ArndW]

Use

Code: Select all

Index("Word01,Word02,Word03,Word04,",ds1.field:',')

It is more efficient as well.

[renaudfa]

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%'.

[Yuan_Edward]

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.

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%'.

[Yuan_Edward]

Or if you can afford the cost of Basic Transformer, the server built-in routine MatchField does the trick:)

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.

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%'.

[chulett]

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".

[Marley777]

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:',')

[chulett]

Check the help, the Index function requires three arguments - you are missing the instance or occurance number to look for.

[priyadarshikunal]

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.

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%'.

Yuan,

DataStage too supports regular expressions.
Read the orchestrate manual and look for filter stage details.

[Marley777]

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!!!

[chulett]

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...

Code: Select all

Index(Convert(",",@FM,"Word01,Word02,Word03,Word04"),ds1.field,1)

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.

[Marley777]

Thanks Chulett, I will give this a try.

[ArndW]

If I could get this code posted by Arndw to work...

Since I neglected the last parameter it won't compile and run, as Craig has posted.

[ray.wurlod]

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.