[SalesForce] In Wave/Einstein analytics, is it possible to combine query results from 2 different datasets into the same visualization

I am creating charts/tables on a Wave Dashboard using a small number of datasets that I've created using dataflows and recipes. What I want to do now is to query (i.e., somehow get data) from two of these datasets and combine these numbers in the same chart/table. Here's a simple illustration:

Dataset 1 – unique contacts – unique on contact_ID.
Dataset 2 – contact history – unique on combination of contact_id and campaign_id (campaign to contact = many to many)

I want to pull the TOTAL number of unique contacts and combine this with the number of unique contacts for a given campaign.

In traditional SQL, I would do this by combining two independent queries via a UNION. Anyway to do in Wave? I have been playing with the SAQL but can't quite seem to get it. Many thanks!

Best Answer

As you mentioned, you can use the union in SAQL as well. Here is the link to documentation, but it's quite weak. I tackled the same issue last week and here is my solution:

q = load "Dataset_1";
q = group q by 'Year__c';
q = foreach q generate 'Year__c' as 'Year__c', "Dataset_1" as 'Status', sum('Number__c') as 'Number__c';
r = load "Dataste_2";
r = foreach r generate 'Year__c' as 'Year__c', "Dataste_2" as 'Status', sum('Number__c') as 'Number__c';
result = union q, r;
result = group result by ('Year__c', 'Status');
result = foreach result generate 'Year__c' as 'Year__c', 'Status' as 'Status', sum('Number__c') as 'Number__c';
result = order result by 'Year__c' desc;

First, the dimensions by which you want to join those datasets have to have same name (at least I couldn't make it work otherwise). Then the next step is to load those datasets, foreach for both and then use a union, where you group by those same dimensions. Group_1 is the

This is how the output looks like in my case: enter image description here

In Dataset_2 I have data only for Year__c 2017

Hope this helps. I'm also new to Analytics so maybe I could have done something better. Feel free to correct me.

Also, you can give it a try with cogroup but I couldn't make it work.

Related Topic