number of null values... optimized

[#1]

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?

[settu_periasamy]

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

NullCount()

From the Qv help..

[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

[#4]

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.

[#5]

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

[#6]

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]

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.