topic Re: Problem with null values / NOT null values in SET ANALYSIS in QlikView

Problem with null values / NOT null values in SET ANALYSIS

Wed, 14 Jan 2015 13:42:55 GMT

Hi everybody.

Im still having problems with this expression.

I have to SUM Quantity, with Concept = 'Concept'. And Field has to be NULL.

I'm doing this

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

Now i have to do the same but Field has to be NOT NULL

I'm doing this

SUM({<Concept= {'Concept'},Field-= {""}>}Quantity)

I have the SAME VALUE with the two expresions...

Somebody knows how can i solve this problem?

Thank you.

Greetings.

Re: Problem with null values / NOT null values in SET ANALYSIS

alexandros17 — Wed, 14 Jan 2015 13:53:38 GMT

this expression (your first expression gives you all NOT NULL values)

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

the second expression is the same of the first

You can have Null by difference, that is:

SUM({<Concept= {'Concept'},Field= >}Quantity)

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

Let me know

Re: Problem with null values / NOT null values in SET ANALYSIS

Wed, 14 Jan 2015 14:00:40 GMT

Hi.

This is kinda strange.

I have a negative result.

And if this is ALL VALUES - NOT NULL this doesn't make sense.

Greetings!

Re: Problem with null values / NOT null values in SET ANALYSIS

Wed, 14 Jan 2015 14:05:23 GMT

I think that SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity) gives me ALL NULL VALUES

I should do this

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

SUM({<Concept= {'Concept'},Field= >}Quantity)

what do you think?

Re: Problem with null values / NOT null values in SET ANALYSIS

alexandros17 — Wed, 14 Jan 2015 14:09:45 GMT

Field={'*'}-{''} means that you select all values comeing from the first set '*' (are all the values) minus values from the second set '' (no values) ...

Verify your data with a fixed selection

Re: Problem with null values / NOT null values in SET ANALYSIS

jerem1234 — Wed, 14 Jan 2015 15:01:48 GMT

Hi Jeremias,

You have to be careful what you refer to as null values. Blanks, blank spaces, and other "empty" strings are not technically null values. They have a value. So what you are doing in:

SUM({<Concept= {'Concept'},Field={'*'}-{''} >}Quantity)

is saying that take everything that has a value in Field (blanks and blank spaces included) and subtracting the set of all blanks from that. So this would be giving you everything that has a value in Field except for blanks (and nulls).

Here are some expressions that may help:

sum({<Field = {'*'}>}Value) - Sum values where Field has some value in it

sum({<Field = {'=len(trim(Field))>0'}>}Value) - Sum where the length of the Field after it is trimmed of spaces is greater than 0 (i.e. everything that has values that aren't blanks or blank spaces).

sum({<Field = {'=len(trim(Field))=0'}>}Value) - Sum where the length of the Field after it is trimmed of spaces is equal to 0. This will sum the blanks and blank spaces in your Field field. This will not include actual null values.

sum({1-$<Field = {'*'}>}Value) - Sum where there are null values. This takes the set 1 (i.e. the whole set of data ignoring selections and subtracting the set of data that has some value in Field. This will give you the actual values where Field is null.

sum({1-$<Field = {'*'}> + $<Field = {'=len(trim(Field))=0'}>}Value) - Sum where there are nulls and blanks and blank spaces.

Please find the attached dashboard for some examples.

Also, NULL handling in QlikView will help. It is written by HIC. Very good read.

Hope this helps!