Organize Macro

QlikView is a great tool for a lot of tasks, but in my opinion it surplus the competition on the initial exploration of a new data source. In the past I used Excel for this task, with the purpose of :

  • Trying to understand every column in a dataset, looking at the column name, but also at the contained data. Even when documentation is available (and most of the times it’s nowhere to be found), looking at the data will give a more clear understanding of what is available.
  • Learning how complete is your data. So you have an “Customer Address” column, good for you. Oh, 30% of the time is empty and 40% is meaningless… That wasn’t on the manual, right?

The process of analyzing a new data set is, at start, a table by table analysis. So I take a big table (from a DB, Excel or CSV file) and load it to QlikView. Now I have a huge list of fields to work with. Since I don’t know the data, they are all good at this point, so I add everything to the first sheet of the document :


The default QlikView behavior is quite messy. It adds everything up, but in a big pile of… stuff.


The next process involves a LOT of drag and drop, moving empty fields to one side, numeric to the other, dates to another corner, dividing over and over the stuff that I know from the stuff that I still need to understand.

The purpose of this post is to show you a small macro that tries to simplify this process, by organizing the list boxes on screen, and changing their color according to some basic data types (numeric, timestamp, text and undefined). After running the macro, the pile of fields will now look something like this :ArrangedFields

In the script there are three parameters that you can adjust for your needs :

  • maxPerRow¬†: How many ListBox are distributed in each row
  • listWidth : How many pixels will the ListBox width take
  • listHeight : How many pixels will the ListBox height take

Have fun, enjoy, explore, expand!


Leave a Reply

Your email address will not be published. Required fields are marked *