Handy data discovery tool

Here's a handy tool to assist in the early development and analysis of the data coming into your Qlikview documents.  There are three objects that you can copy from the attached Qlikview document and paste into your document. You do not need to modify your data model or load script in any way as these objects use standard QV built-in fuctionality.

• A tree-view list box with the tables and fields in your data model
• A text box containing summary statistics for the selected field (hidden until a field is selected)
• A dynamic list box containing the distinct values of the selected field (also hidden until a field is selected)

Here is a Qlikview document containing the 3 objects. Select a field name (any one) to make all three visible and copy from tis document into your Qlikview document.

Data Discovery.qvw

Creating the tools yourself

 If you are using QV Personal Edition, you will not be able to open the attached document, so here is how you can create these objects

The data structure listbox

Create a list box, select in the Field box, and enter the following expression:

=Aggr(Only({1} $Table) & '|' & Only({1} $Field), $Field, $Table)

Then check the "Show as TreeView" option and enter the vertical pipe "|" as the separator.

The text box

Create a text box, and add the following expression:

=Num(Count(Distinct [$(=$Field)]), '# ##0') & ' unique values
' & Num(Count([$(=$Field)]), '# ##0') & ' total values ('
 & Num(Count([$(=$Field)]) / (Count([$(=$Field)]) + NullCount([$(=$Field)])), '0%') & ')
' & Num(NullCount([$(=$Field)]), '# ##0') & ' null rows
' & Num(Count(If(Len([$(=$Field)])=0, [$(=$Field)])), '# ##0') & ' empty values'

On the Layout tab, add the following conditional expression:
Count($Field) = 1

The dynamic list box

Create a list box, select and add the expression:
=[$(=$Field)]

On the Layout tab, add the following conditional expression:
Count($Field) = 1

Conclusion

Remember to select a field in the data discovery list box to see the other two objects. Feel free to use and modify the objects in any way you please. With all the usual disclaimers...