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

Question

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!

Answer 1

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:

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.