topic Re: Show null values as in QlikView

Show null values as

MK9885 — Tue, 27 Sep 2016 18:05:13 GMT

Hello,

I would like to show blank or null values as 'InProcess'

I'm working on Qlik Sense, not Qlik View.

Ex: If there are 2 values in a filed, A and B the third value is Blank. So instead of blank I want to show InProcess on Bar.

I've tried multiple If condition and also IsNull but couldn't make it work.

stalwar1‌

Thanks.

Re: Show null values as

sunny_talwar — Tue, 27 Sep 2016 18:06:24 GMT

Can you maybe make this change in the script itself?

Re: Show null values as

MK9885 — Tue, 27 Sep 2016 18:08:47 GMT

If I make change in script then will it effect all the other null values in other fields?

I just want these Null values to change, not other Nulls in other fields.

Re: Show null values as

sunny_talwar — Tue, 27 Sep 2016 18:13:49 GMT

All I am saying is that whatever dimension you are working with (assuming DimensionName1), may be create a copy of this so that you can still use DimensionName1 and then use the new one (let's call is DimensionName1_New) for this chart. And all you have to do is this in the script

LOAD DimensionName1,

If(Len(Trim(DimensionName1)) = 0, 'InProcess', DimensionName1) as DimensionName1_New

FROM ....

Re: Show null values as

Anonymous — Tue, 27 Sep 2016 18:16:16 GMT

try with alt ..functions in the Pivot or bar chart ?? (replace 'InProcess' in the following)

the following will give Alternative value '1' in case if any value not found...

Eg:

Load the following script:

Alt_Example:

Load * Inline [

Month, LocalSales, ExRate

2013-01-01, 1000, 1.1

2013-02-01, 976,

2013-03-01, 1100, 1.2

];

Expression in bar chart

Sum(LocalSales * Alt(ExRate,1))

Re: Show null values as

MK9885 — Tue, 27 Sep 2016 18:21:19 GMT

Create a copy of that dimension?

Re: Show null values as

sunny_talwar — Tue, 27 Sep 2016 18:22:31 GMT

I just gave you an example my friend

LOAD DimensionName1,

If(Len(Trim(DimensionName1)) = 0, 'InProcess', DimensionName1) as DimensionName1_New

FROM ....

Re: Show null values as

MK9885 — Tue, 27 Sep 2016 18:27:33 GMT

I loaded that in script but the count is not matching with what I've in db.

I've 10 counts of 'InProcess' and I'm getting 0 as count in dashboard.

Cus I'm taking =0 in script?

Re: Show null values as

MK9885 — Tue, 27 Sep 2016 18:37:33 GMT

NullAsValue *;

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET NullValue='Inprocess';

SET NullDisplay='Null';

Can I use above script?

I do not want all null values to change but only one field values as'Inprocess'

Re: Show null values as

MK9885 — Tue, 27 Sep 2016 18:40:30 GMT

I observed that using above script is changing all the null values in data as 'Inprocess'. so not a good idea I guess.

Re: Show null values as

MK9885 — Tue, 27 Sep 2016 20:45:49 GMT

I got it...

with both scrips I tested and it's giving same result. I was using wrong measure so I wasn;t getting the null value as Inprocess.

LOAD DimensionName1,

If(Len(Trim(DimensionName1)) = 0, 'InProcess', DimensionName1) as DimensionName1_New

FROM ....

Or we can use this as well

LOAD Dim,

if(isnull(Dim)< 0,'InProcess',Dim) as Dim1

FROM....

Thanks stalwar1‌