topic How to convert null() to 0 in QlikView

How to convert null() to 0

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to convert null() to 0

Anil_Babu_Samineni — Tue, 23 Oct 2018 08:05:56 GMT

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

Re: How to convert null() to 0

YoussefBelloum — Tue, 23 Oct 2018 08:06:45 GMT

Hi,

try this:

Load

Employee,

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

RESIDENT Another_Table.

Re: How to convert null() to 0

jyothish8807 — Tue, 23 Oct 2018 08:08:35 GMT

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,

Re: How to convert null() to 0

sculptorlv — Tue, 23 Oct 2018 08:09:53 GMT

Hello!

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

Re: How to convert null() to 0

sculptorlv — Tue, 23 Oct 2018 08:14:19 GMT

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

Re: How to convert null() to 0

Anil_Babu_Samineni — Tue, 23 Oct 2018 08:26:56 GMT

Try this way?

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

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

Re: How to convert null() to 0

jyothish8807 — Tue, 23 Oct 2018 08:48:34 GMT

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,

Re: How to convert null() to 0

Mark_Little — Tue, 23 Oct 2018 10:39:35 GMT

Hi,

You could look at the null as value function.

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

Mark

Re: How to convert null() to 0

sculptorlv — Wed, 24 Oct 2018 05:13:44 GMT

Unfortunatelly, both cases are not working

Re: How to convert null() to 0

sculptorlv — Wed, 24 Oct 2018 05:17:15 GMT

I am not sure how to use it..

And the result is the same:

Re: How to convert null() to 0

sculptorlv — Wed, 24 Oct 2018 05:49:53 GMT

Thank you all guys!

I found my problem!

I used two parameters for Employees with the help of JOIN. As the result, for some Employees were not presented in the first LOAD statement.

For my needs I started with the full list of empoyees and then sed rangesum function.