Skip to main content

How to Join Disparate Data with Artificial Joins (using Case Expressions)

  • September 29, 2020
  • 2 replies
  • 512 views

spencer_engel
Forum|alt.badge.img+4

Have you ever felt stuck when using Data Designer or the Rules Engine when you have data from two data sources that just donโ€™t seem to have a common identifier? Or, more likely, you need to aggregate data at an account level for one dataset and then compare it to data aggregated at a higher level (either your entire customer base or perhaps a certain segment). 

 

Either way, you need some kind of way to join that data...but how? Thatโ€™s what this tutorial solves!

 

 

This tutorial stemmed from a real-world use case I put together for a customer who needed to compare ARR that was at risk per customer against the total ARR for the entire customer base. So if Customer ABC had an open risk CTA and had an ARR of $200,000, the customer needed to compare that against their total ARR (letโ€™s say $2 million) and get a percentage of 10% for customer ABC.

 

 

In order to merge these two datasets that were aggregated at different levels, I created what I call an โ€œartificial joinโ€ using a Case Expression in the Rules Engine for both datasets. 

After manually creating that manual โ€œJoinโ€ field, I was able to merge the two datasets together and then do one more transformation to calculate the percentage of at-risk ARR.

 

Voila, hereโ€™s our final dataset!

 

 

Did you find this topic helpful?

2 replies

phani_kumar
Forum|alt.badge.img+3
  • Gainsight Employee โญ๏ธโญ๏ธ
  • 332 replies
  • September 30, 2020

Super @spencer_engel. Nice workaround. 

Thanks.


sai_ram
Forum|alt.badge.img+1
  • Expert โญ๏ธโญ๏ธ
  • 3727 replies
  • October 1, 2020

@spencer_engel Thats a great explanation! Thanks for writing it here.


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings