Search code examples
kql

Correct syntax when using iff function to change a decimal number

I've got a column called TreatmentDelay where the output is a decimal number, and what I want to do is change the value when it's -1 to 0 with an iff function

So if I do

NewTreatmentDelay = iff(TreatmentDelay == -1, 0, TreatmentDelay)

I get the following Error message

"Call to iff(): @then data type (long) must match the @else data type (decimal)"

so if I try

NewTreatmentDelay = iff(TreatmentDelay == todecimal(-1), todecimal(0), TreatmentDelay)

or

NewTreatmentDelay = iff(TreatmentDelay == -1, todecimal(0), TreatmentDelay)

it doesn't work, and the -1 remains unchanged.

What would be the correct syntax to do this?

Solution

  • both cases of the iff need to have the same output type, i.e. decimal in your case.

    you could try this:

    datatable(TreatmentDelay:int)[0,1]
| extend NewTreatmentDelay = iff(TreatmentDelay == -1, decimal(0), todecimal(TreatmentDelay))