Touchless Formatting

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.


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
 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.


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.  

15 thoughts on “Touchless Formatting”

  1. Dear Sir,
    This is really looking interesting. But I have one confusion. In the QlikView scripting window, under main tab we have lots of system variables defined by using SET statement. e.g.-
    SET MoneyFormat=’$#,##0.00;($#,##0.00)’;
    SET TimeFormat=’h:mm:ss TT’;
    SET DateFormat=’DD/MM/YYYY’;
    SET TimestampFormat=’DD/MM/YYYY h:mm:ss[.fff] TT’;

    If I need to maintain one date format through out the app then I format it from here only. Like here I changed the format from
    SET DateFormat=’DD-MM-YY’; to
    SET DateFormat=’DD/MM/YYYY’;

    So my question is-
    1) In which way your approaches are different from this technique?
    2) What new features is available in your approach, I mean what more I can do in comparison with this above mentioned variables ?

    1. Those system variables assign default formats. For example, if no format parameter is specified in a Date() function, the value of DateFormat variable is used.

      The technique shown is used to modify formats already assigned or defaulted through another means (SQL for example).

  2. Thank you, Rob!
    It works perfect!
    I’ve thought that “Autogenerate 0” is wrong, but your example changed my opinion))

  3. Hi Rob,
    excellent solution. I have one problem, I want to have some fields to be always interpreted as text. It seems not to work, numbers are shown as “?”.

    Any solutions?

    Thank you.

    1. Konstel,
      I usually see the “?” when the internal format get assigned as “UNKNOWN”, usually when loading using the FieldValues() function. Can you post the relevant script where you are seeing the issue?

  4. Hi Rob

    Thanks for this excellent tip.

    I registered for a virtual class starting next week. (Performance Tuning)

    I hope will be interacting with you then.

    In b/w i tried your tip and works fine for Num format. But for Date it is not working.

    Could you please help me on the same.

    I’m using the below script to load the date in M/D/YYYY format.

    My source is in a different format (YYYYMMDD)

    After reloading i’m able to get the Sales as $100, But the date format is still YYYYMMDD.

    Am i missing something here?

    Date(0, ‘M/D/YYYY’) as Date,
    Num(0, ‘$#,##0’) as Sales
    AutoGenerate 0;

    LOAD * Inline [
    1,20151201,100 ];

    DROP Table Temp;

    1. Satish,
      The issue is that your Date in the inline load is not being interpreted as a date. Change your inline load to:

      Date#(Date,’YYYYMMDD’) as Date,
      Inline [
      1,20151201,100 ];

      1. Hi Rob

        Thanks for the response.

        I’m actually trying not to perform any transformation in the source data. In this way it will be optimized QVD as you mentioned in the post.

        Here it is Inline load. But my actual source data is from HANA DB where the format is YYYYMMDD.

        Request you to comment if there is a way.


        1. It sounds like HANA has the Date stored as a string. You will need to use the Date#() function to interpret it as a date when reading from HANA, before storing in the QVD.

  5. Thanks Rob

    I am using this in the Load Statement I use to initialise a table prior to concatenating a series of qvds using a for loop.

Leave a Reply

Your email address will not be published. Required fields are marked *