January 5, 2013

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...







4 comments:

  1. Hi, I just found this looking for something completely different and jsut wanted to say...its awesome! I've treid so many times to use items from blogs and find there is always soemthing 'else' that I need to change to make it work. I use this on my 'scratch pad' when trying to sense check data. Thank you!

    ReplyDelete
  2. Your providing such a valuabe information about studying..and also have some good key points to every student.
    Qlikview Interview Questions | Qikview Training Videos | Qlikview online Training Hyderabad

    ReplyDelete