topic Re: Blank or NULL in Set Analysis in QlikView

Blank or NULL in Set Analysis

qlikviewforum — Tue, 25 Feb 2014 15:26:41 GMT

I have a field called "Area_Name". I want to set analysis to be written to Sum where the "Area_Name" is 'Blank' or 'NULL'

Sum(1)

Please help!

Re: Blank or NULL in Set Analysis

SunilChauhan — Tue, 25 Feb 2014 15:31:59 GMT

sum(if(Area_Name='NULL' or Area_Name='BLANK',1))

hope this helps

Re: Blank or NULL in Set Analysis

pover — Tue, 25 Feb 2014 15:36:33 GMT

Filtering Null can be tricky, but you can try the following to select all values that should exclude the null value and - {''} should get rid of the blank values.

sum({$<Area_Name={'*'}-{''}>} 1)

Karl

Re: Blank or NULL in Set Analysis

greg-anderson — Tue, 25 Feb 2014 16:49:50 GMT

If you don't want to get into testing for Null itself (which can be tricky), you can use the NullAsValue function in your data load.

Add these two lines near the before your data load in the script:

NULLASVALUE;

SET NullValue = 'Blank'; (or SET NullValue = ' ' if applicable)

Otherwise, you will need to get into "exists" functions in some cases.

Re: Blank or NULL in Set Analysis

Oleg_Troyansky — Tue, 25 Feb 2014 17:25:58 GMT

Dear experts!

please do not suggest solutions that are known to be "bad practice", such as SUM(IF( ... )). It is known as a common cause of bad performance, and should be avoided at all costs.

The Set Analysis solution that could work in this case would be something like this:

sum({$<Area_Name={"=len(trim(Area_Name))=0"}>} Sales)

Notice the double quotes and the opening equal sign that signify advanced search. The search criteria requires that the length of the trimmed string is equal to 0 - that will cover NULL values and any number of spaces.

Oleg Troyansky

Come and learn advanced Set Analysis with me at the Masters Summit for QlikView - Chicago, April 1-3.

www.masterssummit.com

Re: Blank or NULL in Set Analysis

Oleg_Troyansky — Tue, 25 Feb 2014 17:28:14 GMT

The formula that I suggested, will count the empty values. If the requirement is to cover all other values, then of course the same formula with the logical subtraction will give you the desired result:

sum({$<Area_Name-={"=len(trim(Area_Name))=0"}>} Sales)

Notice the use of the operator "-=" before the {...}

Re: Blank or NULL in Set Analysis

hallquist_nate — Tue, 25 Feb 2014 17:45:41 GMT

The Alternative to this would be to do a Null test in the script, with something like

IF(IsNull(Field), 1,0) as FlagName

To Incorporate this into a field name, you simply do

IF(IsNull(AreaName), 'Blank', AreaName) as NewAreaNameField,

This would give you a new field where if the AreaName field was null, "Blank" would be used, otherwise the correct AreaName value is used. I like this alternative because you can select all the "Blank" Area names in a list box, populate a table with information and then coach individuals on data entry, or correct the entries as needed. You can also count the Blank names and then use that throughout the application. Creating one Set Analysis expression is handy, but you have to create it over and over, every time you want to use it.

Just a different way to look at it....

Good luck.

Re: Blank or NULL in Set Analysis

pover — Tue, 25 Feb 2014 23:50:56 GMT

I don't know why I never made the connection from using the same len() function I use in the script to using it in set analysis. I seem to be missing a couple synapses between neurons. Good thing I'm trying to be more active in Qlik Community again and going to the Masters Summit.

However, and we can leave the details for the Masters Summit, I don't think you can select the unselectable with Set Analysis. The only way to calculate a sum related to a null dimension is to do the following:

sum(Sales) - sum({$<Area_Name-={"=len(trim(Area_Name))=0"}>} Sales)

or for that matter if you just want to filter null values and not empty strings,

sum(Sales) - sum({$<Area_Name-={"*"}>} Sales)

Also, I don't know in what version this became true, but you no longer have to use double quotes for an advanced search in set analysis.

sum({$<Area_Name={"=sum(Sales)>10000"}>} Sales)

gives you the same result as

sum({$<Area_Name={'=sum(Sales)>10000'}>} Sales)

even sum({$<Area_Name={'*'}>} Sales) returns all area names and not just an Area_Name *

This is great if you have to do nested set analysis.

Anyway, some things to talk about at the Master Summit.

Karl

Re: Blank or NULL in Set Analysis

jagan — Wed, 26 Feb 2014 01:59:58 GMT

Hi,

The simple and effective way is arrive a flag in your script like this

LOAD

If(Len(Area_Name) = 0 OR IsNull(Area_Name), 1, 0) AS AreaFlag

FROM DataSource;

Now in expression just use this

=Sum({<AreaFlag={1}>} 1)

Hope this helps you.

Regards,

Jagan.

Re: Blank or NULL in Set Analysis

pover — Wed, 26 Feb 2014 06:17:59 GMT

Hi Jagan,

It isn't necessary to evaluate both len(Area_Name) = 0 and isnull(Area_Name) because the length of a null value is 0.

Karl

Re: Blank or NULL in Set Analysis

qlikviewforum — Wed, 26 Feb 2014 09:30:46 GMT

Thanks Oleg!

Will this work when we are trying to use the "Area_Name" as one of the dimension? Please suggest!

Re: Blank or NULL in Set Analysis

er_mohit — Wed, 26 Feb 2014 09:34:52 GMT

like this if NULL is written in your field then write this

sum({<Area_Name={"","NULL"}>}Value)

Re: Blank or NULL in Set Analysis

preminqlik — Wed, 26 Feb 2014 10:16:08 GMT

hi try this :

sum({<Area_Name={null()}>}Sales)

Re: Blank or NULL in Set Analysis

qlikviewforum — Wed, 26 Feb 2014 10:30:58 GMT

Sorry Prem it is not working!

I am little confused which one is the right one. Can some one help me out with the sample application please?

Re: Blank or NULL in Set Analysis

jagan — Wed, 26 Feb 2014 10:36:55 GMT

Hi,

Did you tried my solution?

Regards,

Jagan.

Re: Blank or NULL in Set Analysis

qlikviewforum — Wed, 26 Feb 2014 11:17:01 GMT

We would like to do that without any change in the load script! Thats the problem!

Re: Blank or NULL in Set Analysis

qlikviewforum — Wed, 26 Feb 2014 11:25:21 GMT

None of the solution provided above are above to handle the NULL values. It is only able to handle the blank.

I tried Oleg solution but it is able to handle only blank. Any suggestions please!

Re: Blank or NULL in Set Analysis

greg-anderson — Wed, 26 Feb 2014 11:41:11 GMT

Hi there! Have you tested my proposal of using NullAsValue?

While it admittedly has a somewhat "brute force" feel to me, it has made it very easy for me to handle data loads from multiple sources where I cannot be 100% certain whether 'empty' columns are blank ('') or actually NULL. Consolidating these into a single value has made many of my analyses much simpler and more consistent.

Re: Blank or NULL in Set Analysis

Oleg_Troyansky — Wed, 26 Feb 2014 16:40:08 GMT

The set analysis condition that's excluding nulls, should work even if the Area_Name is one of the chart dimensions, as long as you don't need to be sensitive to the individual Dimension values.

Re: Blank or NULL in Set Analysis

pover — Wed, 26 Feb 2014 19:10:15 GMT

This is not pretty, but should get the job done because I also could not handle null values with set analysis alone.

sum(Sales) - sum({$<Area_Name-={"=len(trim(Area_Name))=0"}>} Sales)

I've attached a screenshot of my tests.

Karl