7

I am unable to replace null values in cells. I have created a facet to only display cells that have null values. I then went to edit cells > Transform function and tried to use the replace function but it does not seem to be working.

Different things I have tried:

replace(value, null, 'other_text')
replace(value, 'null', 'other_text')

I expected the null values to be replaced with 'other text'

Screenshot:

enter image description here

Chris Smith
  • 399
  • 3
  • 16

1 Answers1

10

You are not replacing the value null, but the string 'null'. The correct syntax for replacing is value.replace('old','new') or replace(value,'old','new'). But replacing doesn't work on null. You should either create a facet for null-values (your current screenshot shows some non-null-values) and fill the expression field with 'new' or you could do something like if(value==null,'new',value).

CennoxX
  • 773
  • 1
  • 9
  • 20
  • 1
    That seemed to work. I was not aware that replacing does not work on null. It is not documented anywhere. Side note, I had tried the value null. I only tried 'null' when the value null did not work. Thanks. – Chris Smith Sep 11 '19 at 06:55
  • 1
    @ChrisSmith Let's say it's implicitly indicated. the `replace()` function/method [requires 3 strings](https://github.com/OpenRefine/OpenRefine/wiki/GREL-String-Functions#replacestring-s-string-f-string-r) or 1 regex and 2 strings. But `null` is not a string. `null` is emptiness, the absence of value. That's why Open Refine has one facet for `null` and another for the empty string "", which looks like null, but which is a real string despite the fact that it contains nothing. – Ettore Rizza Sep 11 '19 at 10:10
  • 1
    @EttoreRizza That makes sense. However, that is not documented anywhere. At the very least it should be mentioned in the function/method link you posted. – Chris Smith Sep 11 '19 at 14:57
  • Please do edit the wiki if you want to add more precisions about this. You can do it! – pintoch Sep 11 '19 at 15:04
  • 2
    I updated the wiki just now to explain further: https://github.com/OpenRefine/OpenRefine/wiki/GREL-String-Functions#replacestring-s-string-f-string-r – Thad Guidry Sep 11 '19 at 20:38