1

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!

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: 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.

  • Thank you! I will try this and post back here if I can make it work. At some point, speed will become an issue, so if anyone has further tips on how to optimize this query, thank you for sharing. – Zachary Rodman Jul 19 '17 at 14:21
  • Glad to hear that @ZacharyRodman. Maybe another approach could be joining the two datasets to one using Dataflow and Dataset Recipe in Data Manager. Check out this trailhead trailhead.salesforce.com/modules/… – mascot Jul 20 '17 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.