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 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.
-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.
Thanks Rob. Very useful for me.
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 ?
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).
Awesome technique, very scalable, thank you Rob!
Hahaha! Brilliantly simple! Cheers Rob.
The Best …
Thank you, Rob!
It works perfect!
I’ve thought that “Autogenerate 0” is wrong, but your example changed my opinion))
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.
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.
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?
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?
TempFormatTable:
NoConcatenate
LOAD
text(‘1234567’) as account_number
AutoGenerate 1
;
mydata:
NoConcatenate
LOAD
1234567 as account_number /*my data is actually loaded from SQL*/
AutoGenerate 1
;
//drop table TempFormatTable;
Thank you!
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.
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?
Temp:
LOAD
Date(0, ‘M/D/YYYY’) as Date,
Num(0, ‘$#,##0’) as Sales
AutoGenerate 0;
Sales:
LOAD * Inline [
ID,Date,Sales
1,20151201,100 ];
DROP Table Temp;
Satish,
The issue is that your Date in the inline load is not being interpreted as a date. Change your inline load to:
Sales:
LOAD
ID,
Date#(Date,’YYYYMMDD’) as Date,
Sales
Inline [
ID,Date,Sales
1,20151201,100 ];
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.
Thanks
Satish
Oh i remember now that in optimized QVD renaming is still permitted.
Please correct me if i’m wrong
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.
Thanks Rob…
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.
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
TempFormatTable:
LOAD
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
Yes, it should have the same result. In this case the Date(0) function will use the default format specified in the “SET DateFormat=…”.
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.
Thanks,
Abhinava
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.
errata: (like a rename fields using mapname)
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!
LIB CONNECT TO ‘cnOLEDB_SQLServer’;
/* Logic for formatting from: https://qlikviewcookbook.com/2016/07/touchless-formatting/ */
Format_DateFact_01:
Load
Date(Today(), ‘M/D/YYYY’) as [calendarDate]
autogenerate 1
;
//TAG FIELD [calendarDate] WITH ‘$date’;
Calendar:
LOAD
MonthStart([calendarDate]) as [BeginOfMonth],
Date(Date#([calendarDate], ‘YYYY-MM-DD’) ,’MM/DD/YYYY’) AS [calendarDateFormat], /* This is my desired output */
*;
SQL
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())
)
Select
[calendarDate],
[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;
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!