Using mv_to_string function to convert multivalue fields to string fields!
09/16/2021
Tableau CRM has a bunch of new cool SAQL functions, one of them being mv to string. (NOTE: This function is currently in beta status so you’d have to contact your Salesforce Account Executive to get it enabled in your org).
What mv to string does is it converts multivalue fields to string fields, and you can use it in a compute expression node This is HUGE and I particularly find it very helpful for the following scenarios:
Scenario A: You have multiple Contacts tagged in an Activity and instead of having multiple rows for each of the contacts, you want all your contacts separated by a “,” or “;” in ONE row, e.g.
Scenario B: You have a multi value picklist field in Activities and these are getting split into rows but you want to see each activity in one row with all the values from that picklist field separated by a “,” or “;”.
To make it easier, let’s use scenario B! As a Sales Manager working at a Fund I want to be able to see which topics are being most commonly discussed in Activities. These topics are being tracked in a multivalue field called “Topics Discussed”. I have created an Events and Tasks Dataset (remember events and tasks are what comprise Activities) to track all the Activities led by my team and I wish to see all my data in a dashboard. My issue is when I run my dataset, each of the topics discussed is appearing in separate rows, so what I will do is 1. Enable mv to string function and 2. Enter mv to string function in SAQL:
q = load "Events_and_Tasks_datasets_Original";
q = filter q by 'derived_ActivityId' == "00T1O00006SWeRlUAL";
q = foreach q generate 'derived_ActivityId' as 'derived_ActivityId', mv_to_string('ActivityRelation.Topics_Discussed__c') as 'ActivityRelation.Topics_Discussed__c', count() as 'count';
q = order q by ('derived_ActivityId' asc, 'ActivityRelation.Topics_Discussed__c' asc);
q = limit q 2000;
Once you finish this and save you’ll see your table will now have the values separated by a comma in one row.
If you have any questions on mv to string, email me to paulinarueda143@gmail.com
