azurekqlazure-data-exploreradx

Decimal Precision in KQL


I am trying to print decimal precision 2 in KQL but not able to achieve. When I use round() function it is rounding the number equal or greater than 5 number qualify.

My requirement is to print number with 2 precision without rounding the value. I used some extra function using string and concatenations but it seems to be too length and expensive operation in terms of CPU.

Anyone please suggest me if we have any direct function or simple conversion for this requirement.

Below is code

datatable(mydecimal:decimal) 
[
0.32000000000000006,
1.6384000000000003,
3.768320000000001,
0.65536000000000028
]
| project orignalvalue=mydecimal, roundvalue=round(mydecimal,2), requiredValue=strcat(strcat_array(array_slice(split(mydecimal,'.'),0,0),'.'),'.',substring(strcat_array(array_slice(split(mydecimal,'.'),-1,1),''),0,2))

Another Query

datatable(mydecimal:real) 
[
0.32000000000000006,
1.6384000000000003,
3.768320000000001,
0.65536000000000028
]
| extend requiredValue=substring(tostring(mydecimal), 0, indexof(tostring(mydecimal),'.')+3)
| project orignalvalue=mydecimal, roundvalue=round(mydecimal,2), requiredValue;

Below is output

enter image description here

########## Adding one more concern on given solution ######

Given query not filling exact decimal precision 2, example if value is 1 then it should print 1.00, if value is 2.8 then 2.80 should cover.

added below query may be too bad design but need any suggestion on this.

datatable(mydecimal:decimal) 
[
0.32000000000000006,
1.6384000000000003,
3.768320000000001,
0.65536000000000028,
1,
1.0,
1.1,
1.10,
500.55
]
| extend roundvalue=round(mydecimal,2)
| extend result = floor(mydecimal*100, 1) / 100
| extend result01 = case(strlen(substring(tostring(result),indexof(tostring(mydecimal),'.'),3))==1, strcat(tostring(result),'.','00'),
                         strlen(substring(tostring(result),indexof(tostring(mydecimal),'.'),3))==2, strcat(tostring(result),'0'),
                         strlen(substring(tostring(result),indexof(tostring(mydecimal),'.'),3))==3, tostring(result),
                         '')
| project orignalvalue=mydecimal, roundvalue, result, result01

Below is output

enter image description here


Solution

  • You can try with the below query to get the required output. It uses floor() function to get the result as shown below:

    datatable(mydecimal:decimal) 
    [
    0.32000000000000006,
    1.6384000000000003,
    3.768320000000001,
    0.65536000000000028
    ]
    | project orignalvalue=mydecimal, roundvalue=round(mydecimal,2), result = floor(mydecimal*100, 1) / 100
    

    Output: enter image description here