Validate exact two digit after decimal point

Hi ​@akshayl09 ,

First question that pops up, are you sure about the decimals in combination with a Long data type, being an integer?

If it's supposed to be able to contain decimals, you could consider changing the column's data type to decimal or number (or whatever the options of your database are).

Then let the application that displays the data define that the sales amount always shows with two digits.

Other question, why define a DQ to check the format of data? A DQ rule would make more sense when it checks the content of the data right?

Kind regards,

Albert

My Question is more about, when Long Data type/ Float Data type is converted to String data type, it trim zero after decimal point or before digit. 

If figure is 1.00 (float datatype (32,2) or long type), after conversion to String format in Ataccama, it changes to 1 instead of keeping 1.00 similar other examples mentioned above and 001 is converted to 1.

It doesn't keep it as it.

If your column Sales Amount is of type Long, so an integer, it will never have decimals. Why even bother to make it show with two decimals, always being zero…??? After conversion to string it doesn't change from 1.00 to 1, because it was 1 already.

By the way, a Float datatype will not have a number of decimals specified, that would be the case with Number/Numeric/Decimal data types. A Float will have an undefined number of decimals.

To make a number with possible decimals show as you describe with always two decimals, you would have to examine the string: if no decimal sign (point or comma) exists, then add ‘.00', if length(string after decimal)=1 then add ‘0’. Something like that.

On this page you can find functions that you can apply: https://docs.ataccama.com/one/latest/common-actions/one-expressions.html

I suppose that for instance you could make use of the decode, case, iif and length functions.

​@Albert de Ruiter Length function is not applicable for long type data type or float data type.  

I have another case to explain why it is important to retain the zero. 

Consider I have ID which should have 10-digit number, but it starts with 000, as zeros are getting trim. It doesn't validate the number correctly and in other programming language like python or java, this doesn't happen. I am not sure why zeros are getting trimmed while changing data type. 

But you converted it to a string right? Then the length function is applicable.

And again: a Long will never have decimals because it is an integer: it cannot have any functional relevance to make it show with decimals (otherwise it shouldn't have been defined as a Long).

If you have a 10-digit number of which the preceeding zeroes are relevant, then it must be implented as a string.

In my previous answer I gave you some guidance on how to proceed once a digit has been converted to a string. Unfortunately not all functions that we consider for granted, like rpad and lpad, are available in Ataccama. The functions that are available can be found via the link. 

Thanks ​@Albert de Ruiter I understand, we are trying to resolve the issue by work-around. I think rpad or Lpad will add the zero, but it will eventually lead 100 % passing of the rule. In this case, no value will invalid as we are managing them through rpad or lpad. we will miss out invalid values through rule.