Skip to main content
I currently have data stored in a table in horizontal format (table 1) that I need to convert to a vertical format (table 2) so I can get it to the final desired format of table 3.











I've tried multiple ways to transform how the data is formatted in table 1 directly to the format of table 3. There may be a way to do this but I can't seem to figure it out and the documentation i've stumbled upon hasn't been much of a help. A potential path forward would be put the data in a intermediary stage as shown in table 2 and then aggregate on 'Scores' and Group by Account (ID). However, I haven't had much success in gettin the rules engine, specifically the pivot task, to do this for me either. Anyone have any suggestions?











Shane



I'm sure people won't necessarily be happy with this but the easiest solution was for me to just add a formula field on the SFDC Account object using the MAX() function. I believe this could've been solved using CASE statments within a Transformation rule but a simple MAX function that takes in three arguements is much simpler.



How many scores do you have? If it's not too many, transform the dataset from Table 1 so that you have a separate set of data for each Score Name (A, B, C, etc) and then merge the data sets on Account and Score Name. Then Transform a last time, grouping by Account and taking the MAX score:









@kate_green stole my idea. I'm calling the cops. :)



Although Min, Max formula functions across columns would be an ideal solution. We did not encounter this use case till now from any one. But agree that this should be an obvious one to add.





An alternate solution to achieve this would be to use a case statement if there are only 3 such columns. (Not very good though).





Case 1: ((A/B) > 1 AND (A/C) > 1) THEN A





Case 2: ((B/A) > 1 AND (B/C) > 1) THEN B





Case 3: ((C/B) > 1 AND (C/A) > 1) THEN C





@kate_green Your proposed solution might not work as what you are suggesting is merging the datasets vertically (Union). We support only horizontal merge as of today in Bionic Rules.Merge will end up resulting in three different Score Name columns and not one column which accomodates all 3 names. Let me know if I am missing something.



If you merge on both fields (Account Id AND Score Name) and Keep All Records From Both Datasets, I think your data will align vertically... because the ID will merge in one column but since there's a unique Score Name, you'll create a new line of data for each score, for each Account, right?



@kate_green - +1 to your solution. That's the way I would do it with adding the score name so that each row would be unique.



Hi @kate_green and @dan_ahrens ,





The Solution that you proposed will not work. The merge will result in 3 different columns for "Score" and will not result in a single column.





It will merge as follows:











Another example:





In the below case, I am trying to merge Account and Company on SFDC Account ID field.





Both objects have a name field. When I try to merge them it gives me the following error:





"Output field labels should be different".





Please find attached the screenshot:











Please let me know if I am wrong or missing anything. We can also have a meeting to discuss the same.





Thanks,





Jitin



Ah....yes, I think you're right @jitin_mehndiratta . I think there is probably a way to get around this using some complex concatenate and multiple merge complexes, but probably just easier to write the data to an MDA table and then reingest it via a second rule.



Reply


Terms & ConditionsCookie settings