Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Making a null value into a real value

I'm stuck. How do I turn a null value into something like "Unlabeled" or "Backlog" when I load it from a table in my script?

21 Replies
Highlighted

May be like this:

If(Len(Trim(FieldName)) = 0, 'Unlabeled', FieldName) as FieldName

Highlighted
Contributor III
Contributor III

Following link maybe helpful..

https://community.qlik.com/thread/73124

Highlighted
Not applicable

Huh. That didn't work... When I look at the data in SQL it doesn't actually say NULL. Maybe it's an empty string or something. I put your code into a Load statement but it didn't work. It looks like it should have though.

Highlighted

Can you check if there is any length here?

LOAD Len(Trim(FieldName)) as Check1

           Ord(Len(Trim(FieldName)) as Check2

Check1 will help you identify if there are any characters which are not spaces, but still not available to view. Check2 will tell you what that character is actually. Once you have this information, can you let us know what all values show up for Check2 for those rows where FieldName is null?

Highlighted

Hi Nick,

If(Len(Trim(FieldName)) = ' ', 'Unlabeled', FieldName) as FieldName


here with in the single codes you can give space bar(just click '   ')most of the databases if they didn't mention default value then that is a Space bar only.


if it is not successful you can ask DB team only.

Highlighted
Not applicable

It is a space actually, not a null value. Your code still didn't work on it though... This is what I did

Left Keep

Table5:

Load

[Application ID] as ITMSNumber,

If(Len(Trim([ITMS Type])) = ' ', 'Unlabeled', [ITMS Type]) as [ITMS Type],

[Application Status];

Highlighted

Can you check with this

Ord(Len(Trim([ITMS Type])) as Check2

Highlighted
Not applicable

Hi Nick,

first thing first, like stalwar1‌ said, you need to take a look on to the type of data you'r looking at. If's a string saying NULL, then a simple replace is more than enough.

  • REPLACE(FIELD, 'STRING TO REPLACE', 'NEW STRING')
  • REPLACE(FIELD, 'NULL', 'Backlog')

If data, actually is a null, you can use the ISNULL function.

  • IF(ISNULL(FIELD), TRUE CONDITION, FALSE CONDITION - Default null)
  • IF(ISNULL(FIELD, 'Backlog', FIELD)

Have a nice one!

Highlighted
Not applicable

It gave me a list with everything unlabeled and some numbers on the right side...