Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

number of null values... optimized

Hi Guys,

I don't have idea anymore, my brain is pretty dry.

I'm doing  QVD audit. I load each QVD, catch the metadata and the structure.

For each field, I take the field Name, number of distinct values. so far so good.

The problem comes when I try to catch the number of null values.

I tried many thing as quering such as :

NULL_COUNT:

LOAD MyField resident Mytable where isnull(myfield);

set NullCount = NoOfRows(MyTable)

It's working but tooooo slow (I need to audit bunch of QVD).

another tried was around NullAsValue and NullValue...

not really successful ...yet (I couldn't find a way to get the frequency of the default value)

n an ideal situation I can get a function : FieldNullValueCount(MyField)

Alternatively, I'm happy if we can find a function to do a count (similar to FieldValueCount how is doing a distinct). with a count of values and the NoOfRows () I can calculate the null ones.

Or something as Subset Ratio, Density, Non-null values from Qliksens

Any thought?

6 Replies
settu_periasamy
Master III
Master III

Not sure about that. But there is a function to find the Null Value Count.

NullCount()


From the Qv help..

Capture.JPG

rwunderlich

Are you loading the metadata from the XML header or by loading the actual data? You can get number of values quickly from the XML header, but I don't see null value counts in there. Tools like QViewer and TIQ Profiler display the number of null values rather quickly, so I image they are looking at the symbol tables directly. Perhaps one of those authors will add something to this thread.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable
Author

Hi, Even if it's a right answer, I cannot use that because it's parsing the table and my script is generic. So it's take as my 1st try a really long run :S.

Not applicable
Author

Thanks Rob, I will have a look.

I'm loading the actual data.... Loading the XML header never comes in my mind.... I will try it. Thanks

Not applicable
Author

In the XML header there is Number of symbols give my the count distinct but I don't see any field for the count.

jcamps
Partner - Creator
Partner - Creator

Hi, I don't know your exact requirements in terms of where you want to output the audit result (in a text file? In a QV table?), but yet the following might work.

Loop through all your fields to build:

  1. A Fields table with: FieldName, FieldId
  2. An expresion variable like so:   =pick ( FieldId, NullValueCount(field1), etc....

Then create a chart with Dimension = FieldName, and Expression = $(=Expression). That should do the trick hopefully...


See attached example.