Dealing with different data types - Qlik View

As we've seen in the previous section, QlikView offers a complete toolbox for dealing with data. In this section we will be looking at some of the most important operators and functions for dealing with strings, numbers, dates, and times.

Strings

Strings are pieces of text; in QlikView these are often used to provide context to the numbers. You may have noticed that in the script, strings are always enclosed between single quotes (').

String operators

The most common operation performed on strings is concatenating two or more strings together into a single string. This is achieved by using the & operator, for example:

[First Name] &' '& [Last Name]

This concatenates the values of First Name and Last Name, with a space between them, into a single string containing the full name.

String functions

The following table shows the most important string functions.

String functions

Information on other string functions can be found by selecting Help | Help from the menu, choosing the Index tab, and searching for String functions.

Of course, all of these functions can be nested. For example, in our Airline Operations document, origin and destination airports follow the following naming convention:

[Name of town], [State or Country]: [Name of Airport]

For example:

New York, NY: John F. Kennedy International or Amsterdam, Netherlands: Schiphol

If we are only interested in extracting the actual name of the airport, the part behind the colon, we could use the following expression:

mid(index([Destination Airport], ':') + 2)

In this example, we first use the index function to retrieve the position of the colon. We then tell the mid function to retrieve the string that starts two positions to the right of the colon (we don't want the colon or the trailing space).

Similarly, we can use nested functions to retrieve the name of the town:

left([Destination Airport], index([Destination Airport], ',') - 1)

This tells the left function to retrieve all characters up to the first occurrence of a comma.

Numbers and numeric functions

QlikView supports the basic arithmetic operators.

Numbers and numeric functions

The following table shows some of the most important numeric functions:

Dealing-with-different-data-types

Besides basic numeric functions, QlikView has an entire range of statistical, financial, and mathematical functions. An overview can be found by opening the Help file by selecting Help | Help from the menu, switching to the Index tab, and searching for Script functions.

Additionally, the Functions tab on the tool pane in the script editor also gives you access to the entire library of functions.

DUAL data type

Besides the usual data types, QlikView has a data type that can be interpreted as both a number and a string—the DUAL data type. This data type is often used for months, where a month field may return both an abbreviation (Jun) and a number (6).Dual values are created using the Dual() function. For example:

Dual('June', 6)

Date and time functions

Date and time are important attributes in a QlikView document. Being able to see how things have evolved over time is practically a mandatory requirement in any BI project.

It is important to understand that, underneath, the DateTime data type is represented by a floating point number.

For example, 12 noon on May 22nd 2012 is stored as 41,051.5. The whole number 41,051 represents the date; it is the number of days that have passed since December 31st, 1899. The fractional part 0.5 represents the time. As a day (24 hours) is 1, an hour is 1/24 and 12 hours is 12/24, which is equal to 1/2 or 0.5.

Knowing this, we can use many of the numeric functions that we saw earlier to perform date and time calculations. For example, we can use the Floor() function to remove the time information from a date.
Besides the numerical functions, QlikView has a broad range of functions that specifically deal with date and time. The following list shows the most common ones. For example, assume that Date equals 10.15 AM on May 22nd, 2012 , which is a Tuesday.

Dealing-with-different-data-types

An overview of all date and time functions can be found by opening the Help file by selecting Help | Help from the menu, switching to the Index tab, and searching for Date and time functions.

Now that we have seen how we can use different statements, functions, and expressions to create QlikView scripts, it is time to see what options we have for debugging our script.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Qlik View Topics