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 1;

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.  


26 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))

    1. Hi IIya,
      I found some cases in un-optimized QVD loads where the Temp formats do not work if the table was created with “AutoGenerate 0”. I’m now recommending “AutoGenerate 1” which seems to work in all cases.

  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?

      1. Hi Rob, I have the same problem with text().

        I learnt the Touchless formatting in this year’s Qlikview Summit in Stockholm, and it’s great. I want to use it for the first time with TEXT(), because I have some columns in my SQL source which are numeric, however, I want to treat them as text in QlikView.

        See the load script below. You can see text and number if you don’t drop the “TempFormatTable”, which already shows that formatting is not working. When I drop the table, I get “?”.
        Any tips on how to overcome this?

        text(‘1234567’) as account_number
        AutoGenerate 1

        1234567 as account_number /*my data is actually loaded from SQL*/
        AutoGenerate 1

        //drop table TempFormatTable;

        Thank you!

        1. Hi Rodienne, Unfortunately the touchless technique does not not work for text(). Text() is not a formatting function, it is an interpretation function. I don’t know of any shortcut technique to apply text() to a field.

  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. Oh i remember now that in optimized QVD renaming is still permitted.

          Please correct me if i’m wrong

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

  6. Just for understanding:
    Script below do the same job. Am I right?

    //Set default format
    SET DateFormat=’DD/MM/YYYY’;
    SET ThousandSep=’,’;
    SET DecimalSep=’.’

    // Load some dummy fields just to assign formats
    Date(0) as OrderDate,
    Date(0) as ShipDate,
    Num(0, ‘00000’) as PostalCode,
    Num(0) as OrderTotal
    AutoGenerate 0;

    Facts: // Load the QVD
    LOAD * FROM data1.qvd (qvd);

    DROP TABLE TempFormatTable; // Drop temp table

    1. Yes, it should have the same result. In this case the Date(0) function will use the default format specified in the “SET DateFormat=…”.

  7. Hi Rob,

    Though the approach is fantastic, is there any way in which I can achieve the same result at the end of the script(rename fields using mapname). I have to use a binary load and the load statement would not be the first one to execute, and it would not then assign the formats.


    1. Hi Abhinava,
      To my knowledge there is no way to way to do this at the end of script. However, if using QlikView, you can change the format on the Document Properties, Numbers tab.

  8. Hi Rob,

    I have used your touchless formatting tip with great success for a several applications. I recently upgraded to QS June 2019 Patch 2 and now the technique no longer works. In my specific case, I am pulling data in the sql format ‘YYYY-MM-DD’ using the OLE DB driver. For some reason, the formatting seems to only match the source format unless I explicitly cast and format the date input field. Is there something wrong with the method below or does this solution no longer apply to the newer versions of QS? I appreciate your help!


    /* Logic for formatting from: */

    Date(Today(), ‘M/D/YYYY’) as [calendarDate]
    autogenerate 1
    //TAG FIELD [calendarDate] WITH ‘$date’;

    MonthStart([calendarDate]) as [BeginOfMonth],
    Date(Date#([calendarDate], ‘YYYY-MM-DD’) ,’MM/DD/YYYY’) AS [calendarDateFormat], /* This is my desired output */
    With Calendar_cte As
    Select CAST(DATEADD(mm, DATEDIFF(mm, 0, ’09/01/2016′), 0) as DATE) As [calendarDate]
    Union All
    Select DateAdd(month,1,[calendarDate])
    From Calendar_cte
    Where [calendarDate] < DATEADD(month, -1, GETDATE())

    [calendarDate] as [A_Date],
    [calendarDate] as [B_Date],
    [calendarDate] as [C_Date]
    FROM Calendar_cte
    option (maxrecursion 10000)
    TAG FIELD [calendarDate] WITH '$date';

    exit script;

  9. Duuuuuude, where has this been? This is an awesome code simplifier.

    I run into scenarios where I am doing a select * as part of a qvd loader, but need to format dates. I end up explicitly loading every field so that I can do Load date(OrderDate) as OrderDate. So I end up with a ton of lines of code.

    I’ve always wondered, why can’t I just format the field somewhere in the code? Now I can!!!

    Thanks a million!

Leave a Reply

Your email address will not be published.