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!

[qlikviewforum]

Thanks Oleg!

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

[er_mohit]

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

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

[preminqlik]

hi try this :

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

[qlikviewforum]

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?

[jagan]

Hi,

Did you tried my solution?

Regards,

Jagan.

[qlikviewforum]

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

[qlikviewforum]

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!

[greg-anderson]

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.

[Oleg_Troyansky]

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.

[pover]

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