When writing a report, there is a function called if(). What is it, how do I use it, and what parameters can it use?
Environment:
Analytics, Logical functions
Oracle B2C Service
Resolution:
The analytics if() function allows you to compare data, and depending on the results, display different data in the report column. The function takes three parameters. The first parameter is the condition, the second parameter is what to display if the condition is met and the third parameter is what to display if the condition is not met. The second and third parameters must be the same type of data (both dates, or both text, etc).
For example, including this function as a column in a report:
if(incidents.status_id='SOLVED', incidents.closed, incidents.updated)
determines if the incident in the current row has a status of solved. If so, it displays the incident closed date. Otherwise, it displays the incident last updated date.
|
Operator |
Definition |
Example |
Example Explained |
|---|---|---|---|
|
= |
The two values are exactly the same. |
if(incidents.status_id='SOLVED', incidents.closed, incidents.updated) |
If the incident status is solved, then display the date the incident was closed. Otherwise display the last time the incident was updated. |
|
!= or <> |
The two values are not exactly the same |
if(incidents.assgn_group_id != 'SALES', incidents.assgn_acct_id, contacts.sales_acct_id) |
If the incident assigned group is not sales, then display the account assigned to the incident. Otherwise display the sales account assigned to the contact. |
|
> |
The value on the left is greater than the value on the right. |
if(count(distinct incidents.i_id) > 5, 'more than five incidents', 'five or less incidents') |
If the number of distinct incident ids in this row is greater than 5, display “more than five incidents.” Otherwise, display “five or less incidents.” |
|
< |
The value on the left is less than the value on the right. |
if(stats.assists < stats.email_assists, stats.email_assists - stats.assists, stats.assists -stats.email_assists) |
If the number of Web Incidents is less than the number of Email Incidents, display the number of Email Incidents minus the number of Web Incidents. Otherwise, display the number of Web Incidents minus the number of Email Incidents. |
|
>= |
The value on the left is greater than or equal to the value on the right. |
if((date_add(answers.expires, 2, WEEKS, 0)) >= sysdate(), 'Answer expires soon', 'Answer does not expire soon') |
If the answer expiration date plus two weeks is greater than or equal to the current time, then display “Answer expires soon.” Otherwise, display “Answer does not expire soon.” |
|
<= |
The value on the left is less than or equal to the value on the right. |
if(opportunities.rep_value <= opportunities.cos , 'Do Not Proceed', 'Proceed') |
If the rep forecast for the opportunity is less than or equal to the cost of the sale, display “Do Not Proceed.” Otherwise, display “Proceed.” |
|
BETWEEN |
The value on the left is between the two values on the right. |
if(incidents.updated BETWEEN '2009-11-26 00:00:00' AND '2009-12-01 00:00:00', 'Thanksgiving Update', 'Normal Update') |
If the incident was last updated between November 26th at midnight and December 21st at midnight, display “Thanksgiving Update.” Otherwise display “Normal Update.” |
|
LIKE |
The value on the left contains the value on the right as demarcated by the wild card character ‘%’. |
if(contacts.email LIKE '%rightnow.com', 'Internal Contact', orgs.name) |
If the contacts’ email address ends with “rightnow.com” then display “Internal Contact.” Otherwise, display the name of the organization. |
|
NOT LIKE |
The value on the left does not contain the value on the right as demarcated by the wild card character ‘%’. |
if(incidents.subject NOT LIKE '%darn%', incidents.subject, 'CENSORED') |
If the incident subject does not contain “darn” then display the incident subject. Otherwise, display “CENSORED.” |
|
IS NULL |
There is no value stored in the field. |
if(incidents.assgn_acct_id IS NULL, incidents.assgn_group_id, incidents.assgn_acct_id) |
If there is no account assigned to the incident, then display the group assigned to the incident. Otherwise, display the account. |
|
IS NOT NULL |
There is a value stored in the field. |
if(incidents.closed IS NOT NULL, incidents.closed, incidents.created) |
If there is a value store in the incidents date closed field, then display the incidents date closed. Otherwise, display the date created. |
|
IN |
The value on the left matches one of the values on the right. |
if(incidents.status_id IN ('WAITING', 'SOLVED'), 'No Update Required','Update Required') |
If the incident status is either waiting or solved, then display “No Update Required.” Otherwise, display “Update Required.” |
