Thursday 15 September 2011

Setting default sort order

Sometimes we load some categorical data (e.g. Country) that we want to display in a specific order other than the default (alphabetical for example).  Here's an example that you might have come across:

  High, Medium, Low

An interesting thing about QlikView is that it remembers how things are first loaded.  We can use this little trick to load a temporary table with just the field that we want in the order that we want.  Then, even if we drop this table later, QlikView remembers that order and we can use the "Load Order" sort option.

Here is an example.  If I had a sales table like this:


Sales:
CrossTable(Year, Sales)
Load * Inline [
   Country, 2009, 2010, 2011
   Ireland, 200, 300, 400
   Scotland, 100, 150, 300
   Wales, 500, 300, 400
   England, 800, 700, 800
];


But, rather than the alpha-sort of England, Ireland, Scotland, Wales, I would like to have Wales, Ireland, England, Scotland, then I can load a table just before the Sales table like this:


SortOrder:
Load * Inline [
   Country
   Wales
   Ireland
   England
   Scotland
];


Then, sometime after I have loaded the Sales table, I can drop the SortOrder table:

Drop Table SortOrder;

Now, I can use the "Load Order" sort:




Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

No comments:

Post a Comment

Note: only a member of this blog may post a comment.