# Converting null to zero in "Expression" and "Column Expression" node.

Hello,
Could you please help in modifying the expression where the null values can be converted to O (zero) in the expression itself (using if … else or anything else) ?

Column-Expressions.knwf (77.0 KB)

How to convert the null value to zero using expression for the below nodes:

“Prev_c1” column, first row, null value should be 0 (zero)

“Prev_c1+c2” column, the first row, the value should be 10 instead of null

How to compute the same (“Prev_c1” & v"Prev_c1+c2" columns) using “Column Expression” node

Hi @ajit555,
If Prev_c1 is a mathematical result (due to the c1 values special structure) `\$["c1"]-1` would do the job. But I don’t think it’s the case. If not (ie previous value is necessary), “value should be 0” is an external information so `if(\$["c1",-1]=MISSING,0,\$["c1",-1])` seems to do the job in the first Expression node.
Syntax is different in column expression :

``````if (isMissing(column("c1",-1)))
{0}
else
{column("c1",-1)}
``````

By the way, I just learn that `column("c1",-1)` works in column expression !! Which is great : thanks for this !

Best,

2 Likes

@ajit555 one way to have the first row as 0 would be this:

`\$["c1",-1] ?? 0`

https://docs.knime.com/latest/knime_expressions_guide/index.html#_operators

4 Likes

You will have to activate multi-row access

2 Likes

Column-Expressions.knwf (76.8 KB)

Problem solved after incorporating the above two solutions.

Thank you so much.

2 Likes

I may suggest a correction for the second line in Column expressions to have exactly the same results including the type of the values :

The two options for the ‘if’ have to be between {}.
Best regards,

1 Like

I am very new to syntax coming from Excel background.

Is there an option to define intermediate variables in the expression for “Prev_c1+c2” column ? e.g. i want to define a variable as -

intervar1 = if (isMissing(column(“c1”,-1))) {0} else {column(“c1”,-1)}
out = intervar1 + column(“c2”)

“out” variable value would be the output for column “Prev_c1+c2”

Yes you could define a intermediate variable. The result “out” is unecessary. See documentation in the description of the node or Column Expressions – KNIME Community Hub
For this node " The syntax and grammar of the expressions are based on the Javascript".

1 Like

Thanks. Below is giving Syntax error:

var1 = if (isMissing(column(“c1”,-1))) {0} else {column(“c1”,-1)}
var1 + column(“c2”)

Try ` if (isMissing(column(“c1”,-1))) {var1=0} else {var1=column(“c1”,-1)}`

1 Like

Yes it worked.

//if (isMissing(column(“c1”,-1))) {0+ column(“c2”)} else {column(“c1”,-1)+ column(“c2”)}
if (isMissing(column(“c1”,-1))) {var1=0} else {var1=column(“c1”,-1)}
var1+column(“c2”)

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.