Equation / Criteria building in IDEA - Tips and Hints Part 3

 

In the earlier two posts we have looked at simple condition building in IDEA for Numeric, Character and Date fields.

 

Today we cover Logical Operators.

 

Logical Operators allow users to bind multiple conditions together in a single combined equation.

 

Most often you would be required to apply multiple conditions to arrive at the desired result since the outcome would be determined by filters on multiple fields in your underlying data. In such situations Logical Operators like .AND. .OR. .NOT. come to assist.

 

Let us take an example.

 

Say in a Bank you would like to identify high value cash deposits for savings accounts for newly opened accounts.

 

If you examine the requirement definition above there are three sub-parts to the definition or control objective ~

 

a. High Value Cash Deposits

 

b. Savings Account

 

c. Newly Opened Accounts

 

One way to run this requirement through IDEA would be to apply each sub - part condition above through a separate filter arrive at an intermediary child file and then apply the next sub - part condition. This is inefficient.

 

So you can build a combined equation in the Equation Editor and deploy it through a Direct Extraction as -

 

Account Type == "Saving" .AND. Transaction Amount > 50000 .AND. Account Opening Date > "20180401"

 

You can see from the Equation above that we have a Character, Numeric and Date condition stringed together in one combined equation using .AND. as the logical operator.

 

.AND. when used in a condition ensures all the values are true or available or occurring in the underlying data for the filter to work effectively.

 

Now consider another requirement definition -

 

Look for blank PAN Numbers or zero Total Income or missing Birth Dates in a Policy Holder Master Data File in an Insurance Company.

 

Here the filter will use another logical operator .OR. rather than .AND.

 

.OR. will give master entries where either the PAN is blank or the Total Income of the Policy Holder is zero or the Birth Date is missing.

 

So this condition will look like PAN = "" .OR. Total Income = 0 .OR. Birth Date = "00000000"

 

Notice the string in the Birth Date condition above is written as "00000000" to represent missing dates in the IDEA date string recommended format as YYYYMMDD or 00000000

 

Now if I tweak the above requirement definition to -

 

Look for blank PAN Numbers AND zero Total Income OR missing Birth Dates in a Policy Holder Master Data File in an Insurance Company.

 

Your condition will look like -

 

PAN = "" .AND. (Total Income = 0 .OR. Birth Date = "00000000")

 

Notice we have placed Total Income = 0 .OR. Birth Date = "00000000" in brackets since it is stringed by an OR condition.

 

In Part 4 of this post series we will examine the expected output when a condition is applied through a Direct Extraction and when the same condition is applied through a New Field Append.

 

Thank You

 

Group Admin Team