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:
Num(0, '#,##0.00') as LineSalesAmount,
Date(Today(), 'DD/MM/YYYY') As OrderDate
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