Replacing null in a column

[Manoj Chaurasia]

Hi everyone,

 

I have a use case where I need to replace the null value to "no data" for a column imported from the database. I used "Is null" & was able to detect the null values & enter "no data", but in a new calculated column. However, my requirement is to do this relacement in the original column itself.

 

Please suggest. Thanks in advance

[Manoj Chaurasia]

You can do so in information link itself. For oracle write a NVL the expression in element.

[Anders Gavare]

Depending on what you mean by replacing the original column itself, a Transformation (instead of a Calculated column) could perhaps be sufficient:

Insert -> Transformations. Select to add a "Calculate and Replace column" with the expression

 

if ([colname] is null, "no data", [colname])

and set the column name to the same column name as the old column.

(Also, in recent versions of Spotfire (at least 7.6), you can replace null values in the Data Panel by clicking on the column, and selecting 'Specific value' under the 'Empty values' section, and enter the string "no data". These empty values however are not a Transformation as above, but only make the replaced values appear in the final table.)

[Guillaume Blondel]

You can use the SN function : ex. if you want to replace null by 0 : SN([col to be checked],0)

[Prashant Ajjannavar]

Thanks for the answer, it worked.