Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Dealing with different data types

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.

Function	Explanation	Example	Result
`len(string)`	Returns the length of a string.	`len('QlikView')`	8
`left(string, number of characters)`	Starting from the left of the string, returns the specified amount of characters.	`left('QlikView', 4)`	Qlik
`right(string, number of characters)`	Starting from the right of the string, returns the specified amount of characters.	`right('QlikView', 4)`	View
`mid` `(string, starting character, number of characters (optional))`	Returns a substring from the string, starting at the specified character. Optionally, the length of the substring can be specified. If no length is specified, the right-most part of the string (starting at the specified position) is returned.	`mid('QlikView', 5, 2)` `mid('QlikView', 5)`	Vi View
`index(string, substring, occurrence (optional))`	Returns the position at which the substring is found in the string. If an occurrence is specified, QlikView will look for that specific occurrence, otherwise the first occurrence is assumed. If a negative number is supplied for occurrence, QlikView starts searching from the end of the string. If no match is found, the function returns `0`.	`index('QlikView', 'i')` `index('QlikView', 'i', 2)` `index('QlikView', 'i', -1)`	3 6 6
`upper(string)`	Converts the string to upper case.	`upper('QlikView')`	QLIKVIEW
`lower(string)`	Converts the string to lower case.	`lower('QlikView')`	qlikview
`capitalize(string)`	Capitalizes each word in the string.	`capitalize('QlikView document')`	QlikviewDocument
`replace(string, search string, replace string)`	Replaces the search string in the string with the replace string.	`replace('QlikView', 'Qlik', 'Click')`	ClickView
`keepchar(string, characters to keep)`	Returns the string without the characters that are not specified in the keep list.	`keepchar('QlikView', 'ike')`	ikie
`purgechar(string, characters to purge)`	Returns the string minus the characters specified in the purge list.	`purgechar('QlikView', 'ie')`	QlkVw
`textbetween` `(string, start text, end text, occurrence (optional))`	Returns the substring found between the start and end text. If an occurrence is specified QlikView will look for that specific occurrence, otherwise the first occurrence will be assumed.	`textbetween('<Qlik><View>', '<', '>')` `textbetween('<Qlik><View>', '<', '>', 2)`	Qlik View
`trim(string)`	Returns the string without any leading and trailing spaces.	`trim(' QlikView ')`	QlikView
`ltrim(string)`	Same as the trim function, but only removes leading spaces.	`ltrim(' QlikView ')`	QlikView
`rtrim(string)`	Same as the trim function, but only removes trailing spaces.	`rtrim(' QlikView ')`	QlikView

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

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([Destination Airport], 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.

Operator	Explanation	Example	Result
+	Add	2 + 2	4
-	Subtract	10 - 5	5
*	Multiply	5 * 5	25
/	Divide	25 / 5	5

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

Function	Explanation	Example	Result
`Ceil()`	Round up. Optionally, a parameter can be specified to indicate which multiple to round up to.	`Ceil(2.5)` `Ceil(2.6, 0.25)`	3 2.75
`Floor()`	Round down. Optionally, a parameter can be specified to indicate which multiple to round up to.	`Floor(2.5)` `Floor(2.6, 0.25)`	2 2.5
`Round()`	Round the number. Optionally, a parameter can be specified to indicate which multiple to round to.	`Round(3.14)` `Round(3.16, 0.1)` `Round(3.14, 0.1)`	3 3.20 3.10

Function

Explanation

Example

Result

Ceil()

Round up. Optionally, a parameter can be specified to indicate which multiple to round up to.

Ceil(2.5)

Ceil(2.6, 0.25)

2.75

Floor()

Round down. Optionally, a parameter can be specified to indicate which multiple to round up to.

Floor(2.5)

Floor(2.6, 0.25)

2.5

Round()

Round the number. Optionally, a parameter can be specified to indicate which multiple to round to.

Round(3.14)

Round(3.16, 0.1)

Round(3.14, 0.1)

3.20

3.10

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.

Tip

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.

Function	Explanation	Example	Result
`Year()`	Returns the year part of the date.	`Year(Date)`	2012
`Month()`	Returns the month part of the date.	`Month(Date)`	5
`Week()`	Returns the ISO week number of the date.	`Week(Date)`	21
`Day()`	Returns the day of the month.	`Day(Date)`	22
`Weekday()`	Returns a number between 0 (Monday) and 6 (Sunday), representing the day of the week.	`Weekday(Date)`	1
`Hour()`	Returns the hour part of the time.	`Hour(Date)`	10
`Minute()`	Returns the minute part of the time.	`Minute(Date)`	15
`Today()`	Returns today's date, without a timestamp.	`Today()`	2012-05-22
`MakeDate()`	Creates a date from the supplied year, month, and day. If no day is specified, the first day of the month is assumed. If no month is specified, the first month of the year is assumed.	`MakeDate(2012, 5, 22)` `MakeDate(2012, 5)` `MakeDate(2012)`	2012-05-22 2012-05-01 2012-01-01

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Dealing with different data types

Create new playlist

Sign In

Sign Up