Business Pain Point :
Many organizations encounter challenges when dealing with multi-picklist values in reports. The inherent flexibility of multi-picklists can lead to difficulties in summarizing and analyzing data effectively. Businesses often find it challenging to derive meaningful insights when attempting to aggregate or group data based on these multi-picklist values. Reporting becomes less intuitive, and the limited options for filtering, sorting, and summarizing can hinder the efficient extraction of specific information. Additionally, inconsistencies in data entry and interpretation can compromise data integrity, making it harder for businesses to rely on accurate insights for decision-making.
Use Case for Splitting into Different Columns:
To overcome these pain points, organizations can benefit from splitting multi-picklist values into different columns in reports.
Use Case :
In HR, if a multi-picklist records employee skills such as "Programming," "Marketing," and "Design," splitting them into separate columns assists in talent management.
Impact:
Organizations can identify skill gaps, plan training programs, and make informed decisions about team compositions.
In each of these cases, splitting multi-picklist values into separate columns enhances the ability to analyze and derive actionable insights. It allows for a more detailed and nuanced understanding of various aspects of the business, facilitating better decision-making and strategy development.
Solution Using ARRAY_CONTAINS():
In Einstein Analytics, there is indeed a function named array_contains(), and it's used to determine if a specified value is present in an array. Here's a simple use case to illustrate how you might use array_contains():
Use Case: Analyzing Product Preferences
Imagine you have an Einstein Analytics dataset representing employee skills with a multi-picklist field called Employee_Skills__c, capturing skills like "Programming," "Marketing," and "Design."
Dataset Example:
Each record includes the employee's name and a multi-picklist field Employee_Skills__c indicating their skills.
SAQL Solution :
You want to create a dataset or dashboard that shows employees with programming skills.
The SAQL query uses array_contains() to filter records where the array Employee_Skills__c contains the value 'Programming.'
The resulting dataset or dashboard will show employees who possess programming skills.
Solution in Recipe : array_contains(mvField, literal)Â
mvField (Multivalue Field): This is like a list of skills that each employee has. For example, it could be a column in a spreadsheet or a field in your HR system that shows all the skills an employee has, like "Programming," "Marketing," or "Design."
literal (Literal Value): This is the skill you're looking for. In our case, it's "Programming." It's the specific thing you want to check if it's in the list of skills.
Now, array_contains(mvField, literal) is like asking a question: "Does the list of skills (mvField) include the skill 'Programming' (literal)?"
If the answer is "Yes," then the employee has programming skills.
If the answer is "No," then the employee doesn't have programming skills.
Solution In Recipe
1 .Create new column using transformation block in recipe. created a new filed with all the values from the multivalue field in string
2 .Create new formula with IF(arry_contains(Multivalue Filed, 'literal'), true value, false value)
The formula is used to create a new column in transformation block that indicates whether an employee has programming skills. Let's break it down:
array_contains(mvField, 'Programming'):
This part checks if the list of skills (mvField) for an employee includes the skill 'Programming.'
if(array_contains(Employee_Skills__c, 'Programming'), 'Yes', 'No')
If the skill is found:
The formula returns the value specified for true value
If the skill is not found:
The formula returns the value specified for false value
If the employee has "Programming" skills in the Employee_Skills__c field, the new column will have the value 'Yes.'
If the employee doesn't have "Programming" skills, the new column will have the value 'No.'
This creates a clear indicator for each employee, making it easy to filter or analyze those who have programming skills. This kind of formula is handy for creating categorical columns based on specific conditions in your data.