Solved

Concatenate In Rules or Reporting Engine?

  • 11 January 2018
  • 10 replies
  • 290 views
kate_green
Rank
Badge
I think it would be valuable to be able to concatenate in the rules engine.  My use case is below:



I would like to transform data from multiple lines to populate a single text or multi-picklist field to load to SFDC.  For example, the user below has three lines of data, one for each type of training they completed.  I would like to be able to turn that into one line of data for this user that has a field that lists the training they completed separated by commas or by semi-colon.  



Turning this:





Into this:







Thank you!
icon

Best answer by dan_ahrens 11 January 2018, 22:18

View original

10 replies

dan_ahrens
Rank
Userlevel 7
Badge +2
Hi Kate,



So we do have a text concatenation formula in bionic rules available today. How many unique training titles do you have potentially in use? The text concatenation formula has a max of 10 elements that can be combined. 



As long as your list of trainings is less than 10, this can be done via bionic rules through a series of filters, formula fields, merges and transformations. You wouldn't need an MDA table until the very end to store the final output. References to tables in steps 2 and 3 refer to bionic rule steps (in memory).



See below:



VP of Customer Success at Forcepoint
kate_green
Rank
Badge
Wow, awesome - this might just work.  



I do have 13 training titles (and probably more to come).  Do you think I can just split them into two groups and repeat the process twice, combining the output from the first concatenation with the second? 
dan_ahrens
Rank
Userlevel 7
Badge +2
Would getting the data into a single row (basically step 3 above) be sufficient or do you need to get to the final step 4 where ALL the trainings they've taken are represented in a single field?



If you need to get to step 4, then you could add additional transformation steps to knock out 10 concatenations at a time and keep adding to that list. 



What will get hairy is for steps 2 and 3 you'll need one transform and one merge per training category. So if you had 13 different trainings today, this would total be a ~28 step bionic rule!
VP of Customer Success at Forcepoint
kate_green
Rank
Badge
For my purpose here we need to get all the way to Step 4, in order to compare this field to another in SFDC. 



What's the current limit for tasks in a bionic rule?  



I could also create an intermediary MDA table that holds the results of Step 3 (one line of data per user).  



And then apply Step 4 to the data in that table... 
kate_green
Rank
Badge
Hey Dan,



I tested this out for 3 training titles and ran the rule.  It works as expected, except there are no commas and spaces separating the values:



Formula Field Setup:





From the S3 Connector Export:





In the MDA table where I loaded the data:





Is there something I missed? 



Thanks!
nitin_pawar
Hi Kate,



When you are using concat function I suppose right now you are configuring it as concat (c1,c2)

if you need them to be separated by a space or comma 

you can change the inputs given to concat function like concat(c1,,,c2)



if you notice the second argument you have to type yourself. It can be space as well then it will look like contact (c1, ,c2)



Let us know if that helps 
jitin_mehndiratta
Rank
Hi kate,



You do not need to create temp tables for each type of training. This can be accomplished by using the Pivot task in the Bionic Rule as follows. For each training type, you just need to add a new picot condition. Below is how you can accomplish this:



https://lh5.googleusercontent.com/WV8aDQ0HAQ-GwtVte5usbV2JEO6F0LhunNegvodwxpasiPB2LvUIyOJA7Zr9rgcAy3DEVED5RwoYWCCvtzH38Dgc4WXEc_GZlPaLDXDK2xmrqWDPriJwFEIZFDEJF-a3vp6C9KlL



https://lh3.googleusercontent.com/cvIFExwLS2SHtqL6SPhe3D-xjgZflEKI1V-usfMaBrHDS5Bpa-o0ONSdhOTtrf1WoOb5GzM3sqsm3xpXVeLxIN_z9pJHHQZb-LhBTFy3-uUMf7mHlu9ZC_MLLmwuNHc2cBnFCqob



https://lh6.googleusercontent.com/1TZooGfKuHTaKyHDH5o4KanHdj459lVBORvCnbMOQ-x7LStM2m54ITU2MbtrJjBZ17sp5k_5rjm0HRQQlQDI2evyTjDDfvVnKR_ysuNyPDx392onxpyhyrwufDDyR9cCxld_lpgp



  

https://lh6.googleusercontent.com/9kypgL9koRjphIe2c-JbwEFzLdnio5DC6l7l-jMtzZft8peoy6VjVa-X7hEuQTfxCzXBiFOYwoNoudjgeOZxqNfKvTUiCRBNyHf26-otujn8ObGVU3PylAREUyzGb0ugee5LtlgD



https://lh6.googleusercontent.com/asJZQLG8e0_r2O4VrKOAujC4fJLMUhehO87Bx4sKb8l_BvcViX8olXRfE3IuSW3htUR7Ss7ydyqN9b9b06QX3n7aBn7drPXeSj--hwoVbf_SJbWXhyF3pkIcHINa_epAEsp9svWW



Below will be the Output:



https://lh5.googleusercontent.com/pdW_WyVd_2dGXtD9RX5DwVHAg_etcseZ5dGGtEzSp9VVmG1WXqvtG5IC3i9RywQ6VHfblZo7znx2w9D3RwiGNUZsE61bUoPouikYLIpl70wfjdkPLz6qXxnGpd4c8cFPoRHoOabS
dan_ahrens
Rank
Userlevel 7
Badge +2
You can have up to 15 tasks in a single bionic rule.
VP of Customer Success at Forcepoint
kate_green
Rank
Badge
Thanks!  I was trying to do this but didn't realize I could press "tab" on my keyboard to move to the next field.  Good to know for next time.



I not actually going to use the commas this time, however, because it takes up one of the ten allowed concat fields, and also because not every training type will have a value, which results in a lot of repetitive commas:



kate_green
Rank
Badge
Thank you Jitin! 



I have set up my rule as you described:





I used the original dataset as the left dataset in my first merge, because not all users would have a value for all training types. 



I also used semi-colon in the actual original fields for each training type.  For example, instead of saying "Core", I updated the subjects to include "Core;".  This way, when I concatenate the text without commas, the semi-colon acts to separate the values.  They can now be used in a lookup in SFDC. 



Thank so much for all of your help Dan, Jitin, and Nitin! 

Reply


Terms & ConditionsCookie settings