Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewforum
Creator II
Creator II

Blank or NULL in Set Analysis

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!

34 Replies
qlikviewforum
Creator II
Creator II
Author

Thanks Oleg!

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

er_mohit
Master II
Master II

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

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

preminqlik
Specialist II
Specialist II

hi try this :

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

qlikviewforum
Creator II
Creator II
Author

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
Luminary Alumni
Luminary Alumni

Hi,

Did you tried my solution?

Regards,

Jagan.

qlikviewforum
Creator II
Creator II
Author

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

qlikviewforum
Creator II
Creator II
Author

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
Luminary Alumni
Luminary Alumni


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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Luminary Alumni
Luminary Alumni

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