Thursday, 15 September 2011

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

3 comments:

  1. Hi there
    This tip looks really interesting and I would like to make use of it. Can you show how the FormatTable affects the formatting of the data being read in?
    Thanx
    Alexis

    ReplyDelete
  2. Hi Alexis,

    It is easy to show yourself! Just try the example in QlikView.


    Stephen

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete