When creating a Computation you may need to write it in SQL through the Advanced mode when the Conditional does not allow you to write the logic you would like. Check this article to find out what SQL structure is recommended! 

Prerequisites

• You are a MadKudu user with Admin or Architect permissions
• You know what Computations are for 
• You know basic SQL coding

In the page to create a computation, select Advanced for the Mode of Configuration. You'll see an empty block where you can write SQL logic to create a computation. 

Here are the accepted logics: 

CASE WHEN..THEN..ELSE..END

A CASE statement allows writing a logic "IF this condition is verified THEN output1 ELSE output2", where the output can be a value or a field. 

• A condition can include AND and OR. 
• Format: 
  
  • Important:
    • the order of the WHENs is important: the output for a record will correspond to the first WHEN condition verified
    • The output must be of the same type of the computation 
      
      • The output for a boolean computation would be 1 = true or 0 = false or NULL
      • The output for a numeric computation would be an integer or NULL 
      • The output for a string computation needs to be within ' '  or NULL
    • numeric computations using attributes or other computation should use a CAST ( field AS NUMERIC) to make sure the fields are compatible. 

CASE 
  WHEN [condition1] THEN [output1]
  WHEN [condition2] THEN [output2] 
  ...
  WHEN [conditionN] THEN [outputN]
ELSE [outputDefault]
END 
 

• Example of a numeric computation combining MadKudu's enrichment and your Salesforce enrichment, where MadKudu's source is prioritized and your Salesforce data as a fallback. 

CASE 
   WHEN CAST(employees AS NUMERIC) >0 THEN CAST(employees AS NUMERIC)
   ELSE CAST(employees_salesforce AS NUMERIC)
END 

• Example of a string computation combining MadKudu's enrichment and your Salesforce enrichment, where MadKudu's source is prioritized and your Salesforce data as a fallback.

CASE 
    WHEN LOWER(industry) NOT IN ('unknown', 'cbit_unknown') THEN industry 
    ELSE industry_salesforce 
END

• Other of a string computation 
  • the ELSE can also be the value NULL
  • note the CAST in NUMERIC when using a numerical condition

CASE 
WHEN industry_salesforce IN ('Insurance') AND CAST (employees_salesforce AS NUMERIC) > 1000 THEN 'Large Insurance Company'
WHEN industry_salesforce IN ('Insurance') AND CAST (employees_salesforce AS NUMERIC) < 1000 AND CAST (employees_salesforce AS NUMERIC) > 100 THEN 'Medium Insurance Company'
WHEN industry_salesforce IN ('Insurance') AND CAST (employees_salesforce AS NUMERIC) < 100 AND CAST (employees_salesforce AS NUMERIC) > 0 THEN 'Small Insurance Company'
ELSE 'unknown'
END

• Example of a boolean computation
  • the function LOWER() allows to not take into account LOWER/UPPER cases in the valuesCASE

WHEN lower(pers_title) like '%tech%' THEN 1
WHEN lower(pers_title) like '%data%' THEN 1
WHEN lower(pers_title) like '%analytics%' THEN 1
WHEN (pers_title  like '% IT%' and lower(pers_title) not like '%military%') THEN 1
WHEN lower(pers_title) like '%information%' THEN 1
WHEN pers_title like '% CIO%' THEN 1
ELSE 0
END

• Example of a boolean computation

CASE 
WHEN salesforce_account_is_target IS NULL THEN NULL
WHEN salesforce_account_is_target = 'true' THEN 1
WHEN salesforce_account_is_target = 'false' THEN 0
ELSE 0
END

COALESCE

The COALESCE function allows to write a logic "IF field1 is null THEN use the field2 instead, IF field2 is null THEN use field3 instead...etc". 

Warning: COALESCE does not work if the null value of the field = "unknown" for example.

e.g if employee= 'unknown' and employees_salesforce = 100, then COALESCE (employee, employees_salesforce) would return 'unknown' instead of 100. -> In doubt use a CASE WHEN statement (see above) to explicit the null value. 

• Format: 

COALESCE (field1, field2, ..., fieldN) 

• Example: you would like to create a computation for the Company size combining both the enrichment you have on the number of employees sitting in Salesforce and MadKudu's enrichment on the company. You would want to use the following computation.
  Warning: the order of the fields in the COALESCE is important.
  
  Where  
  • employees_salesforce = computation from your Salesforce containing the information on the company size 
  • employees = computation from MadKudu's source containing the information on the company size  

CONCATENATION

You might need to use a concatenation operator in an advanced computation.

• Format: 

field1 || field2 || field3

• Example: you would like to concatenate the name of the integrations your leads are using

CASE WHEN LOWER(hg__tech) LIKE '%salesforce%' OR LOWER(tech) LIKE '%salesforce%' THEN 'Salesforce; ' ELSE '' END 
|| CASE WHEN LOWER(hg__tech) LIKE '%marketo%' OR LOWER(tech) LIKE '%marketo%' THEN 'Marketo; ' ELSE '' END
|| CASE WHEN LOWER(hg__tech) LIKE '%amplitude%' OR LOWER(tech) LIKE '%amplitude%' THEN 'Amplitude' ELSE '' END

The results would be:

"Salesforce; " when only Salesforce is detected

"Marketo; " when only Marketo is detected

"Salesforce; Marketo; Amplitude" when all 3 techs are detected

"Salesforce; Amplitude" when Salesforce and Amplitude are detected

"" when nothing is detected

F.A.Q.

What other SQL functions can I use in advanced mode?

Our advanced mode for creating computations only supports functions that are compatible with both RedShift SQL and SQLite.

Need help? Open a ticket here