topic Re: number of null values... optimized in QlikView

number of null values... optimized

Thu, 21 Jan 2016 01:20:09 GMT

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?

Re: number of null values... optimized

settu_periasamy — Thu, 21 Jan 2016 03:53:52 GMT

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

NullCount()

From the Qv help..

Re: number of null values... optimized

rwunderlich — Thu, 21 Jan 2016 04:41:05 GMT

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

Re: number of null values... optimized

Thu, 21 Jan 2016 05:09:29 GMT

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.

Re: number of null values... optimized

Thu, 21 Jan 2016 05:11:04 GMT

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

Re: number of null values... optimized

Thu, 21 Jan 2016 05:34:24 GMT

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

Re: number of null values... optimized

jcamps — Thu, 21 Jan 2016 17:58:26 GMT

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:

A Fields table with: FieldName, FieldId
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.