Qlik Sense Interpretation Functions – Syntax & Example

1. Objective

In our last Qlik Sense Tutorial, we discussed Qlik Sense Day Numbering Functions. Today, we will see Qlik Sense Interpretation Functions. This lesson includes all the important functions used in Qlik Sense for interpreting input values in a certain format. We will discuss each interpretation function of Qlik Sense in this lesson in detail, with relevant examples.

So, let’s start Qlik Sense Interpretation Functions tutorial.

Qlik Sense Interpretation Functions – Syntax & Example

Have a look at Qlik Sense Field Functions 

2. Qlik Sense Interpretation Functions

We use the Qlik Sense interpretation functions in the interpretation of data values that are input in Qlik Sense and formatting them in a specific format. Basically, we interpret the string values and convert into numeric values. This is just the opposite of what formatting functions do, as they convert the numeric values into the string values in a particular format.

Although, interpretation functions also return values in the dual data format i.e. both string and numeric interpretation for one input value. Both kinds of values have their significance. Given in the section below, we will discuss all the Qlik Sense Interpretation Functions in detail.

i. Date# function

This function interprets the input text value for date and returns a numeric value corresponding to that text value.

Syntax:

Date#(text[,format])

Where, text is the string value which you want to be interpreted and converted as numeric value.

Do you know about Qlik Sense Integer Functions

So, using the format parameter, you can specify a format in which the text string must be interpreted.

For example, in the code given below, we have loaded string values for dates and want to get their numeric interpretations in return.

Load *,
Num(Date#(StringDate)) as Date;
LOAD * INLINE [
StringDate
8/7/97
8/6/1997
];

Hence, this will return two fields, StringDate (string value) and Date (numeric value) where string and date values will return.

ii. Interval# function

This function evaluates a text value as a time interval and returns a corresponding numeric value for it.

Let’s revise Qlik Sense Time Zone Function

Syntax:

Interval#(text[, format])

Where, text is the string value which you want to be interpreted and converted as numeric value.

Using the format parameter, you can specify a format in which the text string must be interpreted. If there is no specific format then, short date format (YY-MM-DD), time format (M/D/YY), and decimal separator (.)  in the operating system are considered automatically.

For example,

Interval#( T, ‘D hh:mm’ )

where T=’1 09:00′

Returns the numeric value 1.375 corresponding to the string ‘1 09:00’.

iii. Money# function

This function evaluates a string value for money format and returns a numeric value corresponding to the string value in that particular format.

Syntax:

Money#(text[, format[, dec_sep [, thou_sep ] ] ])

Where, text is the string value which you want to be interpreted and converted as numeric value.

Have a look at Qlik Sense Financial Functions

Using the format parameter, you can specify a format in which the text string must be interpreted. If no format is specified, then the formatting options in the operating system like kr # ##0,00 and $ #,##0.00 are considered automatically.

dec_sep sets the decimal separator which will separate the digits. If no such format is specified, then the system uses MoneyDecimalSep value stored in the system.

thou_sep sets the separator used to separate thousand numbers. If no such separator is mentioned, then the system used MoneyThousandSep value.

For example,

Money#( M, ‘ $#’, ‘.’, ‘,’ )

where M= $35,648.50

Returns, ‘$35,648.50’ as the string value and 35648.50 as the numeric value.

iv. Num# function

This function evaluates a string having numbers and evaluates them to return corresponding numeric values.

Syntax:

Num#(text[, format[, dec_sep [, thou_sep ] ] ])

Where, text is the string value which you want to be interpreted and converted as numeric value.

So, using the format parameter, you can specify a format in which the text string must be interpreted. If no format is specified, then the formatting options in the operating system like # ##0 and #,##0 are considered automatically.

Let’s take a tour to Qlik Sense Table Functions

dec_sep sets the decimal separator which will separate the digits. If no such format is specified, then the system uses DecimalSep value stored in the system.

thou_sep sets the separator used to separate thousand numbers. If no such separator is mentioned, then the system used ThousandSep value.

For instance, the piece of code given below returns,

Num#(N, ‘#.#’, ‘.’ , ‘,’)

where N=35,648.50

Returns, 35,648.50 as a string value and 35648.50 as the numeric value. Please note that the formatting applied is applicable only on the string part and not on the numeric part of the outcome.

v. Text# function

This function solely evaluates a string value and does not convert it into a numeric value. It keeps the values as text only.

Syntax:

Text(expr)

For instance,

Text(Address)

Where Address= FlatNo.152, Crystal Township, Delhi

Will only return a text value i.e. ‘FlatNo.152, Crystal Township, Delhi’. No numeric interpretation corresponding to this will be done.

You must learn Qlik Sense Logical Functions 

Or,

Text( pi( ) )

Will return ‘3.1415926535898’ as string value.

vi. Time# function

This function evaluates a text or string value for time format and returns a numeric value corresponding to it.

Syntax:

time#(text[, format])

Where, text is the string value which you want to be interpreted and converted as numeric value.

Using the format parameter, you can specify a format in which the text string must be interpreted. If no format is specified, then formats stored by default such as hh:mm:ss and in the operating system are considered automatically.

For instance,

time#( T )

where T=09:00:00

Returns a string value as ‘09:00:00’ and a numeric value corresponding to it, which is 0.375.

vii. Timestamp# function

This function evaluates a string in date and time format (making a timestamp) and returns a numeric value corresponding to it.

Do you know about Qlik Sense Hyperbolic Functions

Syntax:

timestamp#(text[, format])

Where, text is the string value which you want to be interpreted and converted as numeric value.

Using the format parameter, you can specify a format in which the text string must be interpreted. If we specify no format, then it automatically considers default formats such as hh:mm:ss and short date format (YY-MM-DD), time format (M/D/YY), and decimal separator (.).

For example,

Load *,
Timestamp(Timestamp#(String)) as TS;
LOAD * INLINE [
StringValue
2015-09-15T12:13:14
1952-10-16T13:14:00+0200
1109-03-01T14:15
];

Returns both string and numeric values for each data value entered inline.

So, this was all in Qlik Sense Interpretation Functions. Hope you like our explanation.

3. Conclusion

Thus, we have learned all the important interpretation functions used in Qlik Sense data handling. If you mention no form of interpretation for a data value in an expression in a data load script, then it takes up as a value having mixed data values like numbers, dates, times, time stamps and strings, using the default settings for formats like, number format, date format, and time format, defined by script variables the operating system.

See also- 

Qlik Sense Mathematical Functions

Reference for Qlik Sense