Qlik Sense Formatting Functions – Syntax and Example
In our last Qlik Sense Tutorial, we discussed Qlik Sense interpretation Functions. Today, we will see Qlik Sense Formatting Functions. We use the formatting functions in Qlik Sense to format numeric values in certain formats like as time, date, money, interval, number etc. Such functions return values in dual format i.e. for every value there is a string and a numeric interpretation. Essentially, the Qlik Sense Formatting Functions convert numeric values (taken as input) into a string representation which represents the input numeric value.
So, let’s start Qlik Sense Formatting Functions Tutorial.
2. Qlik Sense Formatting Functions
i. ApplyCodepage() function in Qlik Sense
We use the applycodepage() function to apply the format i.e. the character set of a codepage onto a chart expression or another page. We commonly use this function when we want to copy a certain character from one section of code to another. In script expressions, we mostly use this function.
Where from the text parameter, you can mention the field or text upon which you want to apply the codepage.
codepage is the character set that is to be applied on the field or text.
For instance, we have a piece of code, where we want to apply character sets of respective languages to three rows. We will do it for each row using the applycodepage function.
LOAD ApplyCodepage(ROWA,1253) as GreekProduct, ApplyCodepage (ROWB, 1255) as HebrewProduct, ApplyCodepage (ROWC, 65001) as EnglishProduct; SQL SELECT ROWA, ROWB, ROWC From Products;
Thus, this will apply Windows Greek character set, for which the corresponding number is 1253 to the field ROWA. Similarly, 1255 and 65001 are the standard UTF-8 character sets for Hebrew and English/Latin respectively which we will apply on ROWB and ROWC fields respectively. These three fields are loaded from SQL and are not in the required character sets. So, we use this function to apply the standard Windows character sets.
ii. Date() function in Qlik Sense
The date() function specifies a date format for a value or expression in the data load script. Like all the formatting functions, this function also returns value in dual format i.e. both text and numeric interpretations.
Where, the number is the value for which you want to set the date format.
Using the format parameter, you can specify the format of the date or else the default formats can assume as saved in the system. There are two types of formats that are in the system by default, they are, YY-MM-DD and M/D/YY.
Example of using this function in a code,
Date( Startday )
This expression will return two values, numeric and text for both the default data types. For the YY-MM-DD setting, the date (string) representation would be 97-08-06 and numeric interpretation would be 35648. Similarly, for the M/D/YY format, the string representation is 8/6/97 and numeric representation is 35648.
You can also specify the date format as given in the code below.
Thus, the string representation corresponding to this number is 06/08/1997 and the numeric representation is 35648.
iii. Dual() function in Qlik Sense
We use the dual() function, as the name suggests to create both textual (string) and numeric representation of a value. Like as we saw in the example above, we had a textual representation ‘06/08/1997’ for the numeric value 35648 for a single date. Dual values are useful to create because in their case, the textual value is used for display or representation purposes and the numeric counterpart of that value is used for storage and calculation purposes.
Where, the text is the textual value that you want to have a numeric part for and the number parameter is the numeric value which you want to use with the string value in an argument.
For instance, in the code given below, we have loaded a field named DayOfWeek which contains weekdays both as string representations and have corresponding numbers to the strings. The numerical part is used in sorting these weekdays numerically and if the weekdays are referred by numbers in another function then these numbers (0 to 6) can be used by the system’s logic to understand which weekday is being mentioned.
Load dual ( DayName,DayNumber ) as DayOfWeek inline [ DayName,DayNumber, Monday,0 Tuesday,1 Wednesday,2 Thursday,3 Friday,4 Saturday,5 Sunday,6 ];
iv. Interval() function in Qlik Sense
The interval() function defines an interval format for date and time intervals which are generally used in chart expressions and data load scripts. Intervals can be formatted in many ways like as time, as a day, as time and day both which has both, the elements of a date and that of time.
Where number is the numeric value which you want to get formatted as interval.
By mentioning the format, you set the type of formatting which you want to apply on the number. If no type of formatting mentioned, then the default formats are applied like, YY-MM-DD for date and hh:mm:ss for time and a number decimal separator.
Interval( Day )
Returns, a string value ’09:00:00’ and a corresponding number 0.375 for this value. Thus, this function return values in dual data formats.
v. Money() function in Qlik Sense
The money() function is used to format values as representation of money. The formatting sets are usually taken from the available system variables in the data load script or in the operating system. If you wish to apply any other money format on the values, you need to specify them in the expression with valid decimal and thousands separator.
Money(number[, format[, dec_sep[, thou_sep]]])
Where, number is the value which needs to be formatted as money.
Format is the specific format which is system defined or mentioned by the user. If you do not specify a format in this function, then the formats saved in the system as defaults are taken up. There are two types of money formats which are available in the system, first is kr ##0,00, MoneyThousandSep’ ‘ and the second is $ #,##0.00, MoneyThousandSep’,’.
dec_sep is the string which specifies the decimal number separator.
thou_sep is the string which specifies the thousands number separator.
For example, suppose we mention the money format for values in the variable, amount.
Money( Amount, '#,##0 $', '.' , ',' )
It will return a string representation as 3,564,800 $ and a number which is 3564800.
vi. Num() function in Qlik Sense
This function formats values as numbers. In this function, you can specify the format for a number or use the pre-defined formatting set provided by the system. This function also returns a dual format value.
Num(number[, format[, dec_sep [, thou_sep]]])
Where, number is the value which needs to be formatted as money.
Format is the specific format which is system defined or mentioned by the user. If you do not specify a format in this function, then the date formats saved in the system as defaults are used.
dec_sep is the string which specifies the decimal number separator. MoneyDecimalSep is used as the default decimal separator.
thou_sep is the string which specifies the thousands number separator. MoneyThousandSep is used as the default thousand separator in the data load script.
For example, in the piece of code given below, we have set a format for the numeric values as 0.00.
Sheet1: let result= Num( pi( ), '0.00' ); Load * inline [Field1; Field2 4; 12.56 1; $(result) ](delimiter is ';');
The results for the field Field1 will be 4 and 1. Results for the field Field2 will be 12.56 and 3.14 (value of pi) according to the format specified in the function.
vii. Time() function in Qlik Sense
The time() function returns a numeric value by formatting it as a time value. The default settings are hh:mm:ss in the system which is taken up if you do not specify a time format in the function expression.
Where, number is the value upon which you want to apply the time format. And using the format parameter, you can specify the format in which you wish time to get displayed.
For example, you can assign the time format to the values in the variables.
Time( T )
Returns a string value corresponding to the numeric value for T= 0.375 which is 09:00:00 as time.
viii. Timestamp() function in Qlik Sense
This function formats a numeric value as a date and time timestamp. The value returns in dual format i.e. it interpret both as a string and numeric value.
Where, number is the numeric value which is to be formatted as a timestamp.
format is the specific format which you want to apply on the value to make it a timestamp. There are two types of formats i.e. YY-MM-DD hh:mm:ss and M/D/YY hh:mm:ss which are available in the system as default formats. You use these if you do not specify a format for timestamp.
This will return two values, for each of the two default timestamp format. For YY-MM-DD hh:mm:ss format the result is, 97-08-06 09:00:00 (string) and 35648.375 (numeric). And for M/D/YY hh:mm:ss format the result is 8/6/97 09:00:00 (string) and 35648.375 (numeric).
So, this was all in Qlik Sense Formatting Functions. Hope you like our explanation.
Hence, in this Qlik Sense Formatting functions tutorial, we learned about all such functions which format a numeric value into a specific type string interpretation. Moreover, these Qlik Sense Formatting Functions are such functions which we use to apply very basic yet important formats on data values. It is important to format data values as it is due to formatting that we can define data in certain ways, such as time, date, money, interval etc. so that it is easy in interpretation.
Share your valuable feedback on Qlik Sense Formatting Functions through comments.
See also –