topic Making a null value into a real value in QlikView

Making a null value into a real value

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Making a null value into a real value

sunny_talwar — Mon, 09 Jan 2017 12:34:07 GMT

May be like this:

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

Re: Making a null value into a real value

ajaysingh29 — Mon, 09 Jan 2017 12:39:20 GMT

Following link maybe helpful..

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

Re: Making a null value into a real value

Mon, 09 Jan 2017 13:33:22 GMT

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.

Re: Making a null value into a real value

sunny_talwar — Mon, 09 Jan 2017 13:37:15 GMT

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?

Re: Making a null value into a real value

ramasaisaksoft — Mon, 09 Jan 2017 13:48:49 GMT

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.

Re: Making a null value into a real value

Mon, 09 Jan 2017 14:01:20 GMT

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

Re: Making a null value into a real value

sunny_talwar — Mon, 09 Jan 2017 14:03:19 GMT

Can you check with this

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

Re: Making a null value into a real value

Mon, 09 Jan 2017 14:03:23 GMT

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!

Re: Making a null value into a real value

Mon, 09 Jan 2017 14:15:53 GMT

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

Re: Making a null value into a real value

Mon, 09 Jan 2017 14:20:08 GMT

I was almost certain that this would be the answer but it didn't work either. I tried this-

REPLACE([ITMS Type], ' ', 'Unlabeled'),

Re: Making a null value into a real value

Mon, 09 Jan 2017 14:24:56 GMT

Nick,

don't worry, could you export the information (2 rows would be fine) to an .csv? A sample may clarify the issue and give you a proper solution.

Re: Making a null value into a real value

girirajsinh — Mon, 09 Jan 2017 14:27:03 GMT

Try

NullAsValue A,B;

Set NullValue = 'Unlabeled' ;

Load A,B from x.csv;

NullAsValue *;

Set NullValue = 'Backlog' ;

Load A,B from x.csv;

Re: Making a null value into a real value

sunny_talwar — Mon, 09 Jan 2017 14:42:57 GMT

Not sure what you mean, can you share a screenshot?

Re: Making a null value into a real value

Mon, 09 Jan 2017 14:43:17 GMT

I hope this helps...

Re: Making a null value into a real value

Mon, 09 Jan 2017 14:48:30 GMT

Hi Nick,

in the sample data you provided the following snippet gets the result you are mentioning:

IF(LEN(TRIM(@1)) = 0, 'Unlabeled', @1) AS APP

That solved the issue?

Re: Making a null value into a real value

Mon, 09 Jan 2017 15:01:21 GMT

Still didn't work. Maybe this screenshot will help?

Left Keep

Table5:

Load

[Application ID] as ITMSNumber,

IF(LEN(TRIM([ITMS Type])) = 0, 'Unlabeled', [ITMS Type]) AS [ITMS Type],

[Application Status];

Re: Making a null value into a real value

Mon, 09 Jan 2017 15:06:15 GMT

Can I see the snippet in the query you are performing?

Also, can you select the null or empty value and create a text box with the code i provided?

Re: Making a null value into a real value

Mon, 09 Jan 2017 15:22:49 GMT

I edited my reply. Please refer to that for the snippet of code.

The box looks like the picture that I attached. Nothing changed.

Re: Making a null value into a real value

sunny_talwar — Mon, 09 Jan 2017 15:25:20 GMT

Nick, I know this might not be very straight forward, but can you just run this:

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

and then make a selection in your ITMS Type field to select the blank option. and then create a list box for Check2 and send us a snapshot of that? This would help us understand what those blanks actually are.