How can we do decimal validation in Ataccama one web DQ rules.
input attribute data type should be float for this rule.
Solved
How can we check decimal value in ONE WEB ?
- Data Pioneer
Best answer by MayKwok
I tested this on my test data set (postgres db source), so I’ll be very interested to see if it works for you.
Let’s say my rule attribute is value1 (float) type.
For my rule condition I’ve put, in Advanced Expression form:
indexOf(toString(value1),”.”) is null
This casts the value1 to string, then check if it contains a decimal point. If it doesn’t contain it, then the expression evaluates to “true”.
In Ataccama ONE DQ rules, the default mode is that if condition is met, then record is INVALID, else VALID. So the above expression will flag any records that doesn’t contain the decimal point, as INVALID.
You may wish to extend the expression to this to also cater for if the database stores the decimal as a comma, like so:
indexOf(toString(value1),”.”) is null or indexOf(toString(value1),”,”) is null
Please give it a try and let us know how it goes! Hope this helped?
- Data Pioneer L2
Hi Mahesh,
Can you elaborate on what you mean by decimal validation? It would be great if you could provide an example of some values that would pass / fail your rule?
Thanks!
- Data Pioneer
Thanks Maykwak for your response .
valid case like : 10.02 ,10,00 (without decimal value should be invalid)
Invalid case like : 10
- Data Pioneer L2
- Answer
I tested this on my test data set (postgres db source), so I’ll be very interested to see if it works for you.
Let’s say my rule attribute is value1 (float) type.
For my rule condition I’ve put, in Advanced Expression form:
indexOf(toString(value1),”.”) is null
This casts the value1 to string, then check if it contains a decimal point. If it doesn’t contain it, then the expression evaluates to “true”.
In Ataccama ONE DQ rules, the default mode is that if condition is met, then record is INVALID, else VALID. So the above expression will flag any records that doesn’t contain the decimal point, as INVALID.
You may wish to extend the expression to this to also cater for if the database stores the decimal as a comma, like so:
indexOf(toString(value1),”.”) is null or indexOf(toString(value1),”,”) is null
Please give it a try and let us know how it goes! Hope this helped?
Login to the Ataccama Community
No account yet? Create an account
For Ataccama Customers and Partners
or
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.