Skip to main content

Hello Community 😀

I am trying to write a DQ rule using a regular expression, I have been using the “ONE Expressions” documentation to help with this but I can’t seem to get it working. Can anyone advise where I am going wrong please??

Example

Input data:

Value1 Value2 Value3
-1137.86 -5689.33 0.2

 

DQ rule I am trying to achieve:

  • Value2 * Value 3 = -1137.866
  • Take only the first 2 decimals of the above bullet WITHOUT rounding = -1137.86
  • Check if value1 equals the value in the bullet above. (i.e. -1137.86 = -1137.86 so it should show as a valid result)

How I have written this in Ataccama using a regular expression:

  • Value1 is NOT (substituteAll("(\d+\.\d{2})(\d+)","${1}",toString(Value2 * Value3)))
  • Then result is INVALID

Error I get:

  • Using the test rule feature with the example input data above, it says that the result is INVALID when I am expecting it to be VALID.
  • And only if I change Value1 to equal -1137.866 then it will show VALID which is not what I want to achieve.

Can anyone see where I am going wrong please? 😵 Thanks!

Hi @Rianna ,

 

Try this expression by using “Advanced expression” in the rule .

Value1 is NOT tofloat((substr(tostring(Value2* Value3),0,lastIndexOf(tostring(Value2*Value3),'.')+3)))

The result is invalid. 

Please find the below screenshots and let me know if this still gives you invalid 

Hope this helps. 

Thanks,

Srija Piratla

Hey @srija piratla 

Thanks for sharing your answer. I have tried this expression and it seems working correctly ! Thanks a loooot :) 

Edit**

The only thing i have found here is this formula is not working for integer values. 

For example when we have 0 or 1 for value2 or value3, the correct result must be in {0,value2,value3}. but I see here when this is the case, expression shows invalid. Trying to add some other checks here, but open for all suggests ! Thanks

Hello @srija piratla 

Thank you so much for helping us! 😃

Reply


Community Terms of Use & ConditionsCookie settings