Blank or NULL in Set Analysis

[qlikviewforum]

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!

[SunilChauhan]

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

hope this helps

[pover]

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

[greg-anderson]

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.

[Oleg_Troyansky]

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

[Oleg_Troyansky]

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 {...}

[hallquist_nate]

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.

[pover]

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

[jagan]

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.

[pover]

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