How to Find Max Value on a Subset of Records

Cool use case @jenniferpa!

There’s a two-step process whereby first you calculate which Interaction Value is the maximum for each company, then second you merge to carry through all of the metadata for that Contact / Person.

This answer depends somewhat on the shape of your dataset, but try this on for size (I’m going to speak in Bionic Rule lingo, but the concept should work anywhere):

1. Within your Data Designer or Rule, you likely have a “final” dataset listing all your active contacts, with a column listing the Company and also a column listing the number of interactions (along with what’s likely a bunch of other metadata).
2. From that formerly “final” dataset, add a transform step beyond it. In that dataset, you’ll pull Group By the Company GSID (and maybe the Company Name too, just for ease of troubleshooting) and Show the MAX of the Interaction Value. Now you’ll know for each Company, the MAX of the Interaction Value (but you won’t know which Contact / Person that represents).
3. Now Merge, using an inside / common merge, merging your formerly “final” dataset and the dataset you created in Step #2 above. You’ll merge on Company ID and also Interaction Value. Select any field you want from your Contact / Person formerly “final” dataset. And ProTip: You can actually de-select any fields from the dataset you created in Step #2 above. Your dataset is now the Contact with the highest Interaction Value, one for each Company.

You’ll end up with something like this (though in this screenshot, I’m looking for the most recently created Contact):

You may have to think through what to do if there’s a tie for the MAX Interaction Value within a Company, and also what to do if no Contacts have an Interaction Value at all (the famous Null conundrum).

Thanks for the quick reply @matthew_lind.  I hadn’t though about using the Interaction Value as an identifier to link back to the contact data.  I am using a large number of factors in the calculation so multiple contacts at the same account having the same score should be an edge case but I will quickly find out after the merge.  

As of now, I am filtering out all contacts with an Interaction Value of zero but, of course, this excludes some of our accounts so I plan to handle that scenario in a future phase. 

I will let you know if your suggestion works.  Thanks again!