Making a null value into a real value

[#1]

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?

[sunny_talwar]

May be like this:

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

[ajaysingh29]

Following link maybe helpful..

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

[#4]

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.

[sunny_talwar]

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?

[ramasaisaksoft]

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.

[#7]

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];

[sunny_talwar]

Can you check with this

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

[#9]

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!

[#10]

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