String Concatenation Tips for Null Handling in CRM analytics/Tableau CRM/Einstein Analytics

Business Use case  :-

You have a requirement where you would need to Augment only sales Data of an Account with respect to same customer and same City. hence We want to have a filed which should contain CUSTOMERNAME  from one column,CONTACTLASTNAME from the next column ,CITY  from the last column  as one string, even if one of the columns stores a NULL.In this case you must perform a string concatenation to have a  join key.

 

Problem Statement : 

If any of the Strings are null, then the result will be null.
 In this blog will demonstrate how we can avoid with end up having null values

Scenario 1 :   String concatenation without separator

Standard Concat Function (Before Workaround):

We’ll use the CONCAT() function. Here’s the formula you’d write:

Syntax: concat(field, ...)

Result :-

Discussion:

The CONCAT() function  concatenating the values of the specified columns and input strings. concat() functions takes only  argument of string . We cannot avoid null elements from being concatenated while using this function.

Solution for Scenario 1:

In order to handle the Null values we can use a Case function within in the Concat Function which converts the Null into empty that can pass a non-null string to the Concat functions.

Formula :

concat(case when CUSTOMERNAME is not null then CUSTOMERNAME else '' end,

              case when CONTACTLASTNAME is not null then CONTACTLASTNAME else '' end,

               case when CITY is not null then CITY else '' end)

 

Result :-

Discussion:

If our string may be null or not null, Use Case functions to handle the null. Then the string is not null further.because the Concate function require that your string is never contains"null",and  It will ignore those empty values that have nothing in them.

Scenario 2 :   string concatenation with separator by using scenario 1 Approach

Use case:

You have a requirement where you would need to have dimension field which is a combination of multiple dimension fields/columns with comma sperator.

If we want to concatenate String arrays, we can simply use the + operator to concatenate them, but we may encounter null values

Problem Statement : 

 If any of the Strings are empty(after handling null), then the result will be concated Strings with have an extra sepearator which passed on the functions . but obviously we want the concated strings which will not have any extra seperator when previous or next string values is empty.

 

Before Workaround:

Concat function takes an additional argument between each strings as a character that serves as a separator between strings. 

Syntax: concat(field1,',',field2,',',field3)

Result :-

Pls refer the below screenshot 1^st Row. Customer Name and city was Empty. So concatenation value was having extra separator as follows   ,Yu,

Discussion:

Now, apart from string values, this function also takes seperator (placed between the string values). This separates one part of the character from another. But as we see, this solution is also not ideal; because when there is a  previous or Next string value is empty it will have an extra seperator which passed on to Concat function.

Solution for Scenario 2:

we have to  pass the Argument (Seperator) when the previous or next String value is not Empty. so that our final concateneated value will not encounter any extra seperators.

Formula:

concat(case when CUSTOMERNAME is not null then CUSTOMERNAME else '' end,

case when CUSTOMERNAME is not null and CONTACTLASTNAME is not null then',' else '' end,

case when CONTACTLASTNAME is not null then CONTACTLASTNAME else '' end,

case when CITY is not null then ',' else '' end,

case when CITY is not null then CITY else '' end)

Result :-

Explanation

In our use case we have to concatenate 3 strings. in this case we would need to pass the two 'seperator' arguments between 1 & 2 , 2&3 strings. before passing seperator arguments to the concat function we have to restric the argument passing when predecessor or successor string is Empty.

1^st separator argument will be passed to the concate function when 1st string and 2nd string is not empty.

2nd  separator argument will be passed to the concate function when 3rd string is not empty.

Please refer below screenshot for the ouput

Discussion:

If our string may be Empty or not Empty , Use Case functions to pass seperator arguments to the concat functions . Then the string is not contain further extra seperator when the previous or next String value .

Summarize.

This article introduces several ways to concatenate strings and handle NULL, Seperators.