Setting default display format

Following on from the last post on Setting default sort order, there are other things that we can load up in advance that can be of assistance.

For example, I have a date field called OrderDate which is coming in with a default format of DD/MM/YYYY hh:mm:ss but the time portion has no use to me because it is just 00:00:00 for all the dates.

Now, I could load this using a simple:

   ...
   Date(OrderDate, 'DD/MM/YYYY') As OrderDate, 

   ...

This is what I would normally do.  However, what if the data is coming from a very large QVD and I would like to keep the optimized load.  Applying a format function like this will prevent the load being optimized.  So, I should do the format in the script that creates the QVD - but what if I am not controlling that?

Simple - just as with the load order, the default format of a field is set when you first load it.  We can create a simple load like this:

   FormatTable:
   Load
Num(0, '#,##0.00') as LineSalesAmount,
Date(Today(), 'DD/MM/YYYY') As OrderDate
   AutoGenerate(1);

Now, I go ahead and load my Order table (note that I have included a format for the sales value too - you can have as many as you need).

Sometime after I have loaded the Order table, I need to drop the Format Table:

Drop Table FormatTable;

I hope this saves you a bit of time.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

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