How to convert null() to 0

[sculptorlv]

Hello..

I have data in my LOAD statements, where one Employee have a value while another has not any records about it.

How can I convert null() values to 0

I have tried the LOAD statement bollow, but it doesn't help.. one employee still has "-" value instead of 0

Load

  Employee,

  IF(Coffee_Rent = null(),0, Coffee_Rent) AS Coffee_Rent

RESIDENT Another_Table.

[Anil_Babu_Samineni]

Is That Real Null Value? May be Use IsNull function like

  IF(Coffee_Rent = null(), If(IsNull(Coffee_Rent),0, Coffee_Rent)) AS Coffee_Rent

[YoussefBelloum]

Hi,

try this:

Load

  Employee,

  IF(len(trim(Coffee_Rent))=0,0, Coffee_Rent) AS Coffee_Rent

RESIDENT Another_Table.

[jyothish8807]

Hi Ruslans,

You can try like this also,

Load

  Employee,

  IF(len(trim(Coffee_Rent)) = 0,0, Coffee_Rent) AS Coffee_Rent

RESIDENT Another_Table.

Br,

KC

[sculptorlv]

Hello!

Qlick says to me that TRIM is not recognized built-in funcion

[sculptorlv]

I've got incorrect syntax erro, need to show 2 parameters

[Anil_Babu_Samineni · Accepted solution]

Try this way?

If(Not IsNull(Coffee_Rent), Coffee_Rent, 0) AS Coffee_Rent

Or

If(Len(Coffee_Rent)>0, Coffee_Rent, 0) as Coffee_Rent

[jyothish8807]

I think it is because you are using a sql statement;

try like this if that is the case.

Load

  Employee,

  IF(len(trim(Coffee_Rent)) = 0,0, Coffee_Rent) AS Coffee_Rent;

SQL  select

*

from table;

Br,

KC

[Mark_Little]

Hi,

You could look at the null as value function.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptRegularS...

Mark

[sculptorlv]

Unfortunatelly, both cases are not working