In our previous examples, we learned to create a simple Data Module for our IBM Cognos Analytics Reports. We are learning about How to create Joins in Data Modules. When we import data, then it is not mandatory we will get all the information we required of reporting. Sometimes we need to add manual calculations to retrieve the correct information and use it in our reports. This article is dedicated for Operators in IBM Cognos Analytics and Data Modules.
Following are the Key Points which we will cover in this article.
An operator is a symbol used to perform a specific mathematical or logical operation on given values.
We will check the different operators to create calculated columns or calculations in IBM Cognos Analytics Data Modules.
Following are the different operators available in Tableau
Mathematical operators are used to performing a calculation on numeric data types.
The plus operator (+) is also used with strings to perform concatenation.
Following are some example using Mathematical Operators.
() Brackets are used to control the expression evaluation order.
+ For additions, and string concatenation
- For subtraction and negation
* For multiplication
/ For division
|| For concatenate
Logical Operators are used to comparing two or more values (numbers, strings, etc.). It will return a Boolean value (True or False) based on compression. The result will be used in the expression for decisions. For example, if a number is less than (<) 10 then increment it by 1. Else do nothing.
Equals To (=): Equals To symbolic representations “=”. It is used to compare if the Left and the right values are equal or not. Example
1=1 Result True
1=2 Result False
‘Hello, vLemonn’=Hi, Mayank’ Result False
Greater Than (>): It is used to compare if the left value is greater than the right one.
1 > 2 Result False
2>1 Result True
Less Than (<): It is used to compare if the left value is greater than the right one.
1<2 Result True
2<1 Result False
Greater Than Equals to (>=): It is used to compare if the left value is greater than or equals to the right one.
1>=2 Result False
(1+1)>= 2 Result True (Equals)
2>=1 Result False (Greater)
Less Than Equals to (<=): It is used to compare if the left value is less than or equals to the right one.
1<=2 Result True (Less)
(1+1) <= 2 Result True (Equals)
2<=1 Result False
Not Equals to (<>): It is used to compare if the left value and the right value are not the same.
1<>1 Result False
1<>2 Result True
‘Hello, vLemonn’<>’Hi, Mayank’ Result True
IS: IS operator is used for testing for NULL values. When we use the IS operator, the right operand must always be either NULL or NOT NULL.
Example 1 IS NULL Result FALSE, 1 IS NOT NULL Result TRUE
IF (1 IS NULL) THEN ('One is Null') ELSE ('One is not null')
IN: IN operator is used for testing if a single value is available in the given list. The list may be a hard-coded value or derived dynamically from an SQL Statement. If the test value or the given value is available in the list the result is TRUE else FALSE.
1 IN (1,10,100,1000) Result True
IF (1 IN (1,10,100,1000)) THEN ('One is available') ELSE ('One is not available')
BETWEEN: BETWEEN operator is used to comparing if the value is available in between two values. In other words, if the given number is within the range of two numbers A and B. Here, A is less than B. The operator will return TRUE if the given number is in between A and B. If the number is outside the range it will return false.
1 BETWEEN 0 AND 2 Result TRUE
IF (1 BETWEEN 0 AND 2) THEN ('Between') ELSE ('Not Between')
Above If is equivalent to IF (1>=0 AND 1<=2) THEN ('Between') ELSE ('Not Between')
LIKE: LIKE operator is used to comparing a string using a wild-carded string value. The result of the LIKE operator is TRUE if the left string matches the right string. We can use the % and _ wildcards. % is for any character sequence. _ Is for any single character.
'vlemonn' LIKE 'v%' returns TRUE
'vlemonn' LIKE 'vl_monn' returns TRUE
'vlemonn' LIKE 'vl__onn' returns TRUE
'vlemonn' LIKE 'vl_onn' returns FALSE
You can use following IF blocks if you are interested in to test the above code with a calculated column or calculation.
IF ('vlemonn' LIKE 'vl_onn') THEN ('Match Found') ELSE ('No Match Found')
Real Time Example
IF ('can u please share vlemonn website. Or https://vlemonn.com is the correct website URL' LIKE '%vlemonn%') THEN ('Match Found') ELSE ('No Match Found')
CONTAINS: CONTAINS operator is used to testing if any specific string is available in the test string or column.
IF ( 'https://vlemon.com/index.php?ID=1&Admin=true' CONTAINS '.php?' ) THEN ('Hacking Attempt') ELSE ('Normal URL')
In the above example, the complete URL is test string or the database table column.
STARTS WITH: STARTS WITH operator is used to test if the given string is starting with a specific string.
IF ('https://vlemon.com/' STARTS WITH 'https') THEN ('Visitor Using SSL URL') ELSE ('Visitor isn't using SSL URL')
IS MISSING: IS MISSING operator is used to testing the value. If the value is undefined, then return true else return false.
IF (Country IS MISSING) THEN ('No Data') ELSE ('Data')
AND: Returns true if all the conditions are true.
OR: Returns true if either of the available condition is true.
It is important to consider the operator's precedence while using it inside a calculation. Why? Check the following calculation and predict the result
A) The result is 60. (((10-2)*2)+4)*5
B) The result is -34. (10-(2*(2+(4*5))))
C) The result is 26. 10+((-2)*2)+(4*5)
D) All the above
If we perform the calculation from left to right, then the answer is (A). Similarly, if we perform the calculation from right to left, then the answer is (B). But in the computer world, some operators have precedence over the others. Following is the operator’s precedence table. Off Corse, the top in the list will evaluate first.
|5||==, >, <, >=, <=, !=, IS, IN, LIKE, BETWEEN (Logical Operators)|
As per the above table, the correct answer is (C). If the two operators have the same precedence, then they are evaluated from left to right. If you want to avoid any confusion, then you can use parenthesis. In the case of parenthesis, the innermost will evaluate first and so on.
We are done with the Operators in IBM Cognos Analytics.
Please login to post your valuable comments.