Summary: I show a scripting technique to assign display formats to loaded data without touching existing load statements.
I coded in SAS for many years and always appreciated the FORMAT statement which allows assigning a display format to a field, independent of loading the field.
FORMAT OrderDate MM/DD/YYYY;
In QlikView and Qlik Sense script, there is an equivalent that is useful to be aware of. It’s not a statement, but a little known trick (so little known I’ve never seen anyone but me do it, although I’m sure others have thought if it).
// Load some dummy fields just to assign formats TempFormatTable: LOAD Date(0, 'MM/DD/YYYY') as OrderDate, Date(0, 'MM/DD/YYYY') as ShipDate, Num(0, '00000') as PostalCode, Num(0, '#,##0.00') as OrderTotal AutoGenerate 0; Facts: // Load the QVD LOAD * FROM data1.qvd (qvd); DROP TABLE TempFormatTable; // Drop temp table
The formats assigned in the TempFormatTable will be inherited by any like-named fields in the QVD Load. I sometimes find this easier than adding formatting function to the QVD Load statement because:
- It maintains the optimized QVD load.
- I can include a master list in the TempFormatTable. There is no error if a field doesn’t exist in the QVD.
- Syntactically simpler.
- I don’t touch the existing Load statement.
I don’t always format this way, but there are a number of scenarios where the technique is useful. A common application is to change formats from one locale to another. For example, loading a QVD created in Europe (with European formats) and assigning US Date and Number formats.
The technique works for any input source; SQL, QVD, xls, etc. It works for both QlikView and Qlik Sense.
You may not ever need this tip, but if you do, I hope it saves you some time and makes your coding easier.
-Rob
Want more Tips & Tricks? Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct). In addition to our two days of core sessions, Bill Lay’s “Tips & Tricks” on Day 3 always teaches me something new.
