Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sculptorlv
Creator III
Creator III

How to convert null() to 0

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.

1 Solution

Accepted Solutions
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

11 Replies
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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
YoussefBelloum
Champion
Champion

Hi,

try this:

Load

  Employee,

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

RESIDENT Another_Table.

jyothish8807
Master II
Master II

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

Best Regards,
KC
sculptorlv
Creator III
Creator III
Author

Hello!

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

sculptorlv
Creator III
Creator III
Author

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

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

jyothish8807
Master II
Master II

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

Best Regards,
KC
mark6505
Partner
Partner

sculptorlv
Creator III
Creator III
Author

Unfortunatelly, both cases are not working

1.jpg

2.jpg