Question

Merge 2 dataset without join condition

  • 27 August 2020
  • 7 replies
  • 163 views

Userlevel 1

Hi,

I have one dataset with my company data and the standard deviation of one of these fields. I want to merge it with another dataset where I have only one record with the Average for the same field but for the whole population.

If I merge them keeping the record with both dataset, I get one line with the average of the population but all the other fields blank and the other lines with all the fields filled in but the average of the population.

Now, what I am trying to achieve, is to compare the average of the whole population with the value of the field per line. So what I need to do is to have that value repeated for each line.

Any idea how can I achieve it?


7 replies

Userlevel 6
Badge +1

While I’m not certain exactly how your datasets are designed, I’d think about a Left Join here (retain all records from left dataset) where your company data is the left dataset and your Average is the right dataset.

You’re essentially instructing Gainsight to keep everything on the left (your company data) and add columns (your Average) where there’s a match on your identified keys.

 

Userlevel 1

Thank you Mattew. The problem is that this solution doesn’t work.

 

One dataset has the average of the whole population. So it is only 1 field and 1 record.

One dataset has data from the company object, so GSID, Name…. Even if I do a left join, I don’t have any Field I can map from one to another.

Userlevel 6
Badge +1

To get where you’re going with a merge, you’ll need *some* key to merge them on, or else you’ll get the sprawl you’re experiencing.

Perhaps tackling from a different angle, you could add a field to your company dataset to hold the Average value. Then author a Bionic Rule which writes the value of your Average to each company’s record. Each time you have a new Average, you can re-run your Rule to update the value within the field.

Userlevel 1

Thank you again Mattew, but forgive my ignorance, how can I write the value on the company object if I don’t have any identifier to upsert or update?

Userlevel 6
Badge +1

If your Average metric applies to all customers, your Bionic Rule can fetch all your customers, which may be represented as Company records. If your Average metric applies to a subset of customers, you can filter as necessary (perhaps you want to exclude Churned Companies, as an example).

Your Action can include a Custom Field (click the Add Custom Field button) which populates the field you create on the Company Object with a static value (your one value representing Average). Whenever you have a new value for your Average, re-run this Rule, updating the static value within the Action.

 

Userlevel 1

Thank you Mattew. But isn’t possible to avoid to manually change every time the value within the action? Ideally having a rule calculating the average for a population and another grabbing that value from the company and using it in another rule.

Userlevel 6
Badge +1

You might want to consider building your own custom object that will house “global” values as well as a primary ID (as mentioned earlier).   You would then wish to store the single values on the Company Object in a dedicated field and ultimately have an ID lookup from Company to the custom object to pull in this global value or values.  You can also leverage the lookup to create calculated fields on reports more easily :)

From here, you can further enhance the data you provide at that level using this as a reference matrix for other data based on the intent of the custom object. 

We employ a similar approach for our engagement model by tier to flatten service levels based on several variables.  I hope this helps? 

Reply