Qlik Sense Conditional Functions – Syntax and Example

FREE Online Courses: Elevate Your Skills, Zero Cost Attached - Enroll Now!

1. Objective

Today, we will see Qlik Sense Conditional Functions. Moreover, we will discuss syntax and example of Conditional Functions in Qlik Sense. Conditional functions evaluate an expression based on a specific condition and if that expression evaluates to be true for that condition then the action will be executed otherwise not.

So, let’s start Qlik Sense Conditional Functions Tutorial.

Qlik Sense Conditional Functions

Qlik Sense Conditional Functions – Syntax and Example

2. Qlik Sense Conditional Functions

i. alt Function in Qlik Sense

The alt() function evaluates a condition for several (alternate) parameters one by one and if the condition matches none of the parameters or expressions then the last ‘else’ value is returned. There may any number of alternative parameters that we can add in the function’s expressions.

The syntax for alt function in Qlik Sense

alt(expr1[ , expr2 , expr3 , ...] , else)

Here, the expr1 will be the first expression to be evaluated, then of the condition for this expression returns false then the second expression, expr2 is evaluated for the given condition. This continues until the logic finds the expression match for the condition or for which the condition evaluates to be True. If none of the given parameters or expression matches the condition, then the expression/value for ‘else’ is returned.

For example,

alt( date#( dat , ‘YYYY/MM/DD’ ),

date#( dat , ‘MM/DD/YYYY’ ),

date#( dat , ‘MM/DD/YY’ ),

‘No valid date’ )

This expression has three parameters or possible expressions for the date format. If an entered data’s format matches any of the three formats the condition will become True and the respective date will be displayed in the proper format. It will return a dual format value having both text and numeric interpretations to of the date. But if the entered date or value does not match these three date formats then ‘No valid date’ note will be returned which is the ‘else’ statement.

ii. class Function in Qlik Sense

The class() function makes classes for data values or to put it in another way, it classifies and sorts the values entered by the users into certain classes defined by this function. This function takes a variable value evaluates it for the interval condition specified for that variable and puts it in that category or class. Let us say that there is a variable ‘Percentage’ and if the percentage = 74 then using the class function you can put this into a range or class of 70 to 80.

The syntax for Qlik Sense class Function

class(expression, interval [ , label [ , offset ]])

This gives the result in a<=x<b

Where expression is the variable or parameter that you define.

Interval is the range that defines the minimum and maximum values of a class, i.e. if you set the interval to be 10 then for every variable value the range will be set for 10 values like 30 to 40 for 34, if you set it 5 then 30 to 35 for variable 34 etc.

The label gives the variable which is generally represented as x a name. Like if x is representing the age of a person then you can set the label as ‘age’.

An offset is a number that can be used as an offset or deviation from the default starting point of the classification. The default starting point is normally set at 0. You can specify the value by how much you want to make an offset from the default value like  5,10 etc.

For example,

We have a list of people with their ages. We want to classify these people based on their age groups, for which we will use the class() function.

LOAD *,
class(Age, 10, 'age') As Agegroup;
LOAD * INLINE
[ Age, Name
25, John
42, Suzen
53, Mark];

Now, this function will return,

NameAgeAge group
John2520<= age <30
Suzen4240<= age <50
Mark5350<= age <60

Where age is the label and each person is classified into age groups as defined in the class() function. The expression was Age, the interval was 10 and label was age.

iii. If Function in Qlik Sense

The if() function evaluates an expression based on certain condition. If the expression evaluates to be True for that condition, then the ‘then’ value is return. But if it evaluates to be False then ‘else’ value is returned.

The syntax for If Function in Qlik Sense

if(condition , then , else)

The condition is the expression which will be evaluated in the logic provided here. Then defines what is to be returned if the condition is true, whereas, else defines what will return if the condition is false.

For example,

If( Batterypercent>=15, ‘Charged’, ‘BatteryLow’)

This statement means that if the battery percent is greater than or equal to 15 then return ‘Charged’ but if it is less than 15 i.e. if the condition is false, then return ‘BatteryLow’.

iv. match Function in Qlik Sense

Match function is used to carry out a case sensitive match. It matches the first expression with the following expressions and returns the number of expression or value that it matches. You can add n number of expressions from which you need to match the first expression.

The syntax for match function in Qlik Sense

match( str, expr1 [ , expr2,...exprN ])

For example,

match( M, ‘Jan’,’Feb’,’Mar’,’Apr’)

This will return 3 if M= Mar because Mar is the third value. And, it will return 0 is M was equals to May which is not in the search result options.

v. mixmatch Function in Qlik Sense

This function performs the same operation as the match() function but it is case insensitive i.e. the case of the value or string you are searching for will not matter. If A= Apple or apple the logic will search it.

The syntax for Qlik Sense mixmatch function:

mixmatch( str, expr1 [ , expr2,...exprN ])

For example,

if fav= Mango then the expression given below,

mixmatch (fav, ‘mango’, ‘apple’, ‘peach’, ‘strawberry’)

Will return the value 1 because it matches the value on the first number or position.

vi. Wildmatch Function in Qlik Sense

Wildmatch function is also a value matching function like match() and mixmatch() but it uses wildcards (* ?) and is case insensitive in searching. The first expression that we search should match with the following possible search result expressions. If the first expression matches any of the following expressions, then the numeric value of their position in the list is returned.

The syntax for Qlik Sense Wildmatch Function:

wildmatch( str, expr1 [ , expr2,...exprN ])

For example,

wildmatch( M, ‘ja*’,’fe?’,’mar’)

It will return 1 of M= January and will also return 2 if M=Feb/Fex/Fen or anything.

vii. pick Function in Qlik Sense

The pick() function picks a particular value from a specific location in the list of expressions. This function selects and returns the expression present on the nth position.

The syntax for Qlik Sense pick Function:

pick(n, expr1[ , expr2,...exprN])

Where n is an integer between 1 and n.

For example,

pick( 2, ‘Sam’,’Jones’, ‘Nick’, ‘Joe’ )

This will return the second value i.e. Jones because of n=2.

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

3. Conclusion – Conditional Functions in Qlik Sense

Hence, in this Qlik Sense Conditional Functions tutorial, we discussed conditional Functions in Qlik Sense such as alt, class, if, match etc. Also, we saw syntax and example of each Qlik Sense Conditional Functions. All these Conditional Functions used in the data load script and charts of Qlik Sense.

Still, if you have any query regarding Qlik Sense Conditional Functions, ask in the comment tab.

Reference for Qlik Sense

Did you like our efforts? If Yes, please give DataFlair 5 Stars on Google

follow dataflair on YouTube

1 Response

  1. Kwanele Ndlovu says:

    Hi there, l want to use the if function for numerical values only, my formulae is picking null values

Leave a Reply

Your email address will not be published. Required fields are marked *