Data Quality - Index function issue in the data rule logic
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 18
- Joined: Thu Dec 08, 2016 11:42 am
Data Quality - Index function issue in the data rule logic
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!
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!
-
- Participant
- Posts: 18
- Joined: Thu Dec 08, 2016 11:42 am
Re: Data Quality - Index function issue in the data rule log
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'
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'
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
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
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
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
-
- Participant
- Posts: 18
- Joined: Thu Dec 08, 2016 11:42 am
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
...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
- 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
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
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
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
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
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
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
-
- Participant
- Posts: 18
- Joined: Thu Dec 08, 2016 11:42 am
-
- Participant
- Posts: 18
- Joined: Thu Dec 08, 2016 11:42 am