Logical Functions

Logical functions are used to check specific logical criteria.

This article will cover the basic principles of working with these functions in the data load editor and visualisations, including:

  • The general function syntax
  • Working with logical functions in the data load editor
  • Working with logical functions in visualisations

For a complete, up-to-date list of logical functions, please see the Qlik Sense help pages here.

The logical function syntax

As of right now, there are only two logical functions. We will decipher the syntax for both functions below.

Example 1: IsNum

The IsNum() function is used to check whether the value is a number. If it is, the function returns true (-1), otherwise, the result is false (0).

Copy code 


IsNum( expr )

The rules for reading the function syntax are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ().
  2. The various elements you will need to enter are separated by commas. In this function, there is only one element: expr.
  3. Anything between square brackets [] is an optional parameter. In this function, there are no optional parameters.

An example of this function being used could be:

Copy code 


IsNum("Phone Number")

The above function will return -1 if the value in the field “Phone Number” is a number and 0 if it isn’t. This could be used to to cleanse that data in the field by removing any values that aren’t numerical.

Example 2: IsText

The IsText() function is used to check whether the value is a string. If it is, the function returns true (-1), otherwise, the result is false (0).

Copy code 


IsText( expr )

The rules for reading the function syntax are as above.

An example of this function being used could be:

Copy code 


IsText("Name")

The above function will return -1 if the value in the field “Name” is a string and 0 if it isn’t. This could be used to to cleanse that data in the field by removing any values that aren’t string.

How to use logical functions in the data load editor

Here are some important points on using logical functions in the data load editor:

  • All of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • They are particularly useful for cleaning data at the extract and transform stages
  • The functions can be nested and combined with other types of functions

A simple example of using the IsNum() and IsText() functions to cleanse the data is:

Copy code 


LIB CONNECT TO 'My_SQL_database';

DATA:
LOAD
	"ID",
    If(IsText("Name"),"Name",Null()) as "Name",
	If(IsNum("Phone Number"),"Phone Number",Null()) as "Phone Number";

  SQL SELECT 
      [ID],
      [Name],
      [Phone Number]

  FROM public.customers;
  
STORE DATA INTO [customers.qvd];

Data will be loaded from the database table customers. Once it has loaded, Qlik checks that the “Name” field values are a string and the “Phone Number” field values are a number. If they are not, Qlik replaces the values with NULL. The clean dataset is then stored in a QVD file for use in apps.

How to use logical functions in visualisations

Both logical functions can be used in visualisations. The syntax is the same as when using them in the data load editor.

  • The functions can be nested and combined with other types of functions.
  • The functions can be used as part of a dimension or a measure depending on your requirement.

Here is an example of both logical functions used in a data table. Note the formula in the column name.