Qlik Sense Conditional Functions – Syntax and Example
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.
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.
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.
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,
|John||25||20<= age <30|
|Suzen||42||40<= age <50|
|Mark||53||50<= 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.
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 ])
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 ])
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 ])
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.
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.