How to replace a string cell with a text value using Calculated Column
Hello,
I need to find 'blank' (empty, no data, '', etc.) cells in a column "Description" (String, 255) and replace with a text 'NO DATA'.
I added InsertRecords Flow --> Column Operations --> Calculated Column and used PQL pad to update cells:
COALESCE(NULLIF(Description,''),'NO DATA') AS Description
However, received the following error
PQL syntax error: no viable alternative at input 'COALESCE(NULLIF(Description,)'. original input COALESCE(NULLIF(Description,''),'NO DATA') AS Description symbol error ) Line 1, position 30
Please note that COALESCE is not available under PQL Common Functions.
Also, did anyone try to use String --> Length common function and update field if a cell Length = 0
I would appreciate the help either to correct my syntax or provide an alternative solution.
4 replies
-
Hi
Are you trying to replace the values as part of a Data Flow in your model?
If so, you could use a PQL expression such as
if(IfNull([Size],-1)=-1,"No Data",[Size])As you can see in the screen shot below, my Size column has no values, in each record it is null. The nulls are replaced with "No Data", using the expression above. Note the checked box to replace the column, not add a new one.
Hope that helps.
Ian
-
Hi
Thank you for your reply.
Yes, I am trying to update column information as part of a Data Flow in my model.
In your model [Size] column have a visible "Null"; however, in my model [Description] column is "blank". I only need to replace "NO DATA" cells where the cell is "blank".
I tried this formula in PQL and it didn't work: if(IfNull([Description],-1)=-1,"No Data",[Description])
