union
Syntax
result = union resultSetA, resultSetB [, resultSetC ...];
Example
q = union q1, q2, q3;
Example
You want to see how each rep compares to the average for deals won. You can make this comparison by appending these two result sets together:Then use union to append the two result sets.
- Total amount of opportunities won for each rep
- Average amount of opportunities won for all reps
First, show the total amount of won opportunities for each rep.
opt = load "DTC_Opportunity_SAMPLE";
opt = filter opt by 'Won' == "true";
-- group by owner
rep = group opt by 'Account_Owner';
-- project the sum of amount for each rep
rep = foreach rep generate 'Account_Owner' as 'Account_Owner', sum('Amount') as 'sum_Amount';
rep = order rep by 'sum_Amount' desc;
The resulting graph shows the sum of amount for each rep.
-- grouping rep by all returns all the data in a single row.
avg_rep = group rep by all;
-- Calculate the average of the Sum of Amount column.
-- Use the text ‘Average Deal Size’ in the ‘Account Owner’ column
avg_rep = foreach avg_rep generate "Average deal size" as 'Account_Owner', avg('sum_Amount') as 'sum_Amount';
Because the two data streams have the same field names and structure, you can use union to combine them.
q = union rep, avg_rep;
The resulting graph contains the sum of amounts by each rep together with the average amount per rep.
Combine the SAQL fragments to get the complete SAQL statement.
opt = load "DTC_Opportunity_SAMPLE";
opt = filter opt by 'Won' == "true";
-- group by owner
rep = group opt by 'Account_Owner';
-- project the sum of amount for each rep
rep = foreach rep generate 'Account_Owner' as 'Account_Owner', sum('Amount') as 'sum_Amount';
rep = order rep by 'sum_Amount' desc;
-- grouping rep by all returns all the data in a single row.
avg_rep = group rep by all;
-- Calculate the average of the Sum of Amount column.
-- Use the text ‘Average Deal Size’ in the ‘Account Owner’ column
avg_rep = foreach avg_rep generate "Average deal size" as 'Account_Owner', avg('sum_Amount') as 'sum_Amount';
q = union rep, avg_rep;
Considerations
Using a union statement and a join statement in the same query has strict enforcements. When a semi or anti join statement is present, the union statement returns an error if there are:
- mismatched number of columns
- mismatched data types
The errors appear as "Different number of fields found across union streams" or "Different types found for field 1: 'fieldName' in different union inputs."
q1 = load "Opportunity";
q2 = load "Opportunity";
q1 =join q1 by (AccountId) semi, q3 by (AccountId);
q2 = group q2 by all;
q1 = foreach q1 generate q1.'AccountId' as 'AccountId',q1.StageName as 'StageName', q1.'Description' as 'Description', q1.'Id' as 'Id',q1.'Amount' as 'Amount', q1.'Probability' as 'Probability';
q2 = foreach q2 generate null as 'AccountId', null as 'StageName',null as 'Description',null as 'Id', sum(unique('Amount')) over([..] partition by all) as 'Amount_total',sum(unique('Probability')) over([..] partition by all) as 'Probability';
q1 = limit q1 5;
qU = union q1, q2;