Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Not sure about that. But there is a function to find the Null Value Count.
NullCount()
From the Qv help..
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
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.
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
In the XML header there is Number of symbols give my the count distinct but I don't see any field for the count.
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:
Then create a chart with Dimension = FieldName, and Expression = $(=Expression). That should do the trick hopefully...
See attached example.