Data Quality - Index function issue in the data rule logic

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
k.prashanthi
Participant
Posts: 18
Joined: Thu Dec 08, 2016 11:42 am

Data Quality - Index function issue in the data rule logic

Post by k.prashanthi »

Hello,

I am using index function to get the substring from a string as shown below in a data rule logic but this is working in some cases and not working in some other cases.

If index(name1,'RCLR') >0 then cash = 0

it's working when the name1 is 'SP Walmart RCLR 2313 OLCL'
and the same logic is not working in the below scenario.

index(name1,'Recycler') >0 then cash = 0
when the name1 is 'Recycler GL Walmart 1068 OLMM'

Can anyone help me with this logic.TIA!
k.prashanthi
Participant
Posts: 18
Joined: Thu Dec 08, 2016 11:42 am

Re: Data Quality - Index function issue in the data rule log

Post by k.prashanthi »

Adding oe more question to this.
If I have two words as a substring then it won't work. Example is given below.

If index(name1,'Walmart RCLR') >0 then cash = 0

when the name1 is 'SP Walmart RCLR 2313 OLCL'
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

Well, the result of Index function is a zero-based index, a zero indicates that the substring was found at the beginning of the string

If you use >= operator instead of just > it will work in all cases

index(name1,'Recycler') >0 then cash = 0
when the name1 is 'Recycler GL Walmart 1068 OLMM'

index(name1,'Recycler') >=0 then cash = 0
when the name1 is 'Recycler GL Walmart 1068 OLMM'



Regards
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
k.prashanthi
Participant
Posts: 18
Joined: Thu Dec 08, 2016 11:42 am

Post by k.prashanthi »

I just tried >= instead of > but it dinot work. Please let me know if you have any other ideas on this.
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

...I would do below

- Test without the second condition "then cash=0" to discard the possibility that this one is causing the rule not to work on the records
- Test with hard coded values
- If nothing work then I would use "matches_regex" instead of Index
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So as a point of clarification for me... is the index function in "data rule logic" different than the parallel version of the function? Because the latter isn't a zero-based index. When zero means the first character position, how does the function indicate there is no occurrence - by returning a null? A negative number?
-craig

"You can never have too many knives" -- Logan Nine Fingers
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

Hi Chulett,

Yes The IA Data Quality rules functions are completely different ...if the substring not exist within the string a -1 will be returned. Is also a different mind set to work with IA data quality rules
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Thanks.
-craig

"You can never have too many knives" -- Logan Nine Fingers
k.prashanthi
Participant
Posts: 18
Joined: Thu Dec 08, 2016 11:42 am

Post by k.prashanthi »

Hi Rodriguez,

Can you please let me know how to use this matches_regex in IA. I started first using matches_regex wih this rule logic and ended up trying Index function as I did not get the correct result with matchs_regex. Thank you!
k.prashanthi
Participant
Posts: 18
Joined: Thu Dec 08, 2016 11:42 am

Post by k.prashanthi »

Issue has been resolved. The weird thing is Index function worked after logging out of IA and relogin. My guess is changes I made were not applied before. It was vey confusing and frustrating though.
Post Reply