topic Handling Nulls in QlikView

Handling Nulls

Thu, 23 Jan 2014 04:12:10 GMT

Hi,

I have a requirement wherein I have to diregard a particular division and display the sum of sales of the other Divisions.

In the sample application attached i am trying to acheive the same by disregarding the Division 'A'

These are the following approaches used by me:

Approach 1:

=sum({<Flag={1},
Division-={'A'}>}Sales)

This approach not only excludes division A but it also excludes the NULL divisions,but all i want is to exclude the Division A and not the NULL divisions

Approach2:

=sum({<Flag={1},
Division={'*'}-{'A'}>}Sales)

This also gives me the result as in approach 1

Approach3:

=sum({<Flag={1}>
-<Flag={1},Division={'A'}>}Sales)

This approach gives me the desired results.But I want to know if there is any built in function within qlikview that could be used to accomplish this requirement in a better way

Re: Handling Nulls

sujeetsingh — Thu, 23 Jan 2014 04:29:24 GMT

Why dont you try removing null in your script

Re: Handling Nulls

Thu, 23 Jan 2014 04:38:19 GMT

I need to show the Nulls also in the application

Re: Handling Nulls

Sokkorn — Thu, 23 Jan 2014 04:38:37 GMT

Hi Anushree,

Use this one in Text object =Sum({$<Flag={1},Division ={'A'}> + -<Division=-{}>}Sales)

I would suggest to create a new flag for Null value in Division field. So that we can control set analysis easily.

Regards,

Sokkorn

Re: Handling Nulls

Anonymous — Thu, 23 Jan 2014 04:43:19 GMT

In the script

NULLASVALUE *;

SET NULLVALUE = '<Unknown>';

LOAD Year,

Division,

Sales,

Flag

FROM

(ooxml, embedded labels, table is Sheet1);

Then the first equation should work....hopefully

Re: Handling Nulls

jagan — Thu, 23 Jan 2014 05:13:58 GMT

Hi,

Try like this

NullAsValue Division;

Set NullValue = 'NULL' ;

Data:

LOAD

If(Len(Division) = 0, Null(), Division) AS Division, Flag, Sales, Year

INLINE [

Division, Flag, Sales, Year

A, 0, 50, 2012

A, 1, 10, 2011

A, 1, 30, 2013

B, 1, 40, 2013

B, 1, 50, 2011

C, 1, 20, 2011

C, 1, 50, 2013

C, 1, 80, 2012

D, 1, 70, 2012

D, 1, 80, 2013

E, 1, 40, 2011

E, 1, 50, 2012

, 1, 30, 2011

, 1, 60, 2012

, 1, 80, 2013

];

Now use this expression

=sum({<Flag={1},Division-={'A'}>}Sales)

Regards,

Jagan.

Re: Handling Nulls

Sokkorn — Thu, 23 Jan 2014 06:20:00 GMT

Hi,

My mistake in previous replied.

If you want to calculate sale amount where Division <> A and Division = Null Then we use =Sum({$<Flag={1},Division -={'A'}> + -<Division=-{}>}Sales)

If you want to calculate sale amount where Division = A and Division = Null Then we use =Sum({$<Flag={1},Division={'A'}> + -<Division=-{}>}Sales)

Regards,

Sokkorn

Re: Handling Nulls

Anonymous — Thu, 23 Jan 2014 06:45:34 GMT

your answer are clean and clear , very helpful !

anant

Re: Handling Nulls

Thu, 23 Jan 2014 06:46:17 GMT

Hi Sokkorn,

The expression suggested by you works completely fine.But,the continuous usage of + and – in the expression,like + -<Division=-{}>}Sales seems a bit confusing to me so could you please explain the logic behind the same

Thanks & Regards,

Anushree Shetty

Re: Re: Handling Nulls

Sokkorn — Thu, 23 Jan 2014 06:54:30 GMT

Hi,

If we want to sum sale amount where division is null then this is the set analysis =Sum({$-<Division=-{}>}Sales)

Now we try calculate other sale amount which division<>A, previously you used =Sum({<Flag={1},Division-={'A'}>}Sales)

Looking into your requirement, you need to sum sale amount base on division<>A and division=null

So I used =Sum({$<Flag={1},Division -={'A'}> + -<Division=-{}>}Sales)

+ sign mean Union

- sign mean Exclusion

See attached file for details

Regards,

Sokkorn

Re: Handling Nulls

Thu, 23 Jan 2014 07:24:58 GMT

Thank you so much for the descriptive explaination

Re: Handling Nulls

Thu, 23 Jan 2014 14:59:23 GMT

Hi Michael,

Very Nice.. Very useful to me..

Is it possible to particular field level.....

Re: Handling Nulls

Anonymous — Thu, 23 Jan 2014 15:05:40 GMT

Instead of NULLASVALUE *;

You can do NULLASVALUE [FieldName1],[Fieldname2],etc..;

Load

IF(ISNULL(Field),'<UNKNOWN>',Field) as Field;

If it is a # field than you can use the Alt() function instead.

Re: Handling Nulls

Thu, 23 Jan 2014 15:08:57 GMT

Yes Yes .. I got it