How to convert String to Multi-Select Picklist using rules

Aim - There are scenarios where you have string values over multiple rows that define an attribute about a company. It could be the products used by a company, any stage that they are simultaneously a part of, etc. Check below for Sample Data - 

Table 1:

The above table entails information regarding Products used by different Companies. 

Our Aim is to Convert the Above Tabular information which has the details of one particular company distributed over many entries to be aggregated into 1 entry by leveraging Multi-select picklist for ‘Products Used’. Check below for the Final data -

Table 2:

Pre-requisite - Create a dropdown with all the products offered by the Organization.

Procedure - Firstly, we would have to convert the multi-row company information into one ROW. This can be achieved by Pivot operation in Rules Engine (Bionic Only; Horizon Rules (BETA) had some issues although the same operation was implemented).

Action on Table 1 - Pivot.

Pivot ON - Products Used. 

Group BY - Company ID, Company Name

The Pivot would convert Table 1 into the table below:

Table 3:

We have achieved Single ROW, company information on products used. The ‘1’ acts as Yes, whereas ‘0’ acts as NO.

On Table 3, we should perform a Transform Operation wherein we must create as many ‘Case Expressions’ as there are products offered. In our case, it is 4. The idea is to convert under the fields (Products) as below: 

Before Transformation: 

Post Transformation:

We are using ; at the end because a Multi-select picklist separates two different fields with a semi-colon. 

NOTE - The custom value should be equal to the string in the dropdown defined in Gainsight dropdown.

Sample Case Expression: 

As previously mentioned, the no. of case expressions would be the same as no. of fields derived in the Pivot Action; with only Case 1 with the product name and semi-colon, and NULL in Default Case. 

Transform Operation on Table 3:

Fields - Company ID, Company Name, Benchmark Starter (CE), Falcon Pro (CE), Pro Rata Service (CE), Neu Rocket (CE)

Here, CE refers to Case Expression.

Post Transformation, the sample data would look like this:

Table 4:

This brings us to our Final Operation, which will be ‘Transformation’. 

The aim of this transform operation is to merge all the products into one column, separated by a semi-colon; as a multi-select dropdown would do. This would be achieved through Concat Operation present in transformation.

Transformation on Table 4:

Fields - Company ID, Company Name, Products (Concat Operation)

Concat Operation:

Concat(Benchmark Starter, Falcon Pro, Pro Rata Service, Neu Rocket)

Note - The concat operation must be performed on all the fields that are derived in the Pivot operation. It is 4 in this case. 

Post Transformation, the dataset would look like this:

Table 5:

The above dataset is analogous to how a Multi-select dropdown stores values in back-end. 

When the above table is used to update the Company Object with the appropriate mappings; We will achieve Table 2.