Monday Morning Challenge: Join two data streams with SAQL in Salesforce Einstein Analytics

Question

I do have two data streams q1 and q2. I want to merge q2.SUM into q1 if they both have the same value in MaxMon.

It looks so obvious, but I am lost in between all this cogroup and union magic. This is how I load those two data streams:

q = load "RetentionDATA1";
q1 = group q by 'Month_of_Retention';
q1 = foreach q1 generate q1.'Month_of_Retention' as 'q1.RM', sum(q1.'MONTHLY_FEE') as 'q1.SUM', toString(toDate(max(q1.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q1.MaxMon';

q2 = filter q by 'Month_of_Retention' == "1";
q2 = group q2 by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month');
q2 = foreach q2 generate toString(toDate(max(q2.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q2.MaxMon', sum(sum(q2.'MONTHLY_FEE')) over([.. 0] partition by all order by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month')) as 'q2.SUM';

So far so good. Now I want to do the ‘merging’. Below are some examples and outcomes of my attempts:

result = cogroup q1 by ('q1.MaxMon') , q2 by ('q2.MaxMon');
result = foreach result generate first(q1.'q1.RM') as 'q1.RM', sum(q1.'q1.SUM') as 'q1.SUM', sum(q2.'q2.SUM') as 'q2.SUM)';

The results with q1.RM=3,4,8 are missing. I might understand 3 and 4, as those have a MaxMon matching two times. But why 8? (Same result if I do it with the keyword left for left join.)

Any other idea, how to join those tables, to get to the result described above? I need a table like this: q1.RM | q1. Sum | q2.SUM where q1.MaxMon equals q2. MaxMon

This is the full query:

q = load "RetentionDATA1";
q1 = group q by 'Month_of_Retention';
q1 = foreach q1 generate q1.'Month_of_Retention' as 'q1.RM', sum(q1.'MONTHLY_FEE') as 'q1.SUM', toString(toDate(max(q1.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q1.MaxMon';


q2 = filter q by 'Month_of_Retention' == "1";
q2 = group q2 by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month');
q2 = foreach q2 generate toString(toDate(max(q2.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q2.MaxMon', sum(sum(q2.'MONTHLY_FEE')) over([.. 0] partition by all order by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month')) as 'q2.SUM';

result = cogroup q1 by ('q1.MaxMon') left, q2 by ('q2.MaxMon');
result = foreach result generate first(q1.'q1.RM') as 'q1.RM', sum(q1.'q1.SUM') as 'q1.SUM', sum(q2.'q2.SUM') as 'q2.SUM)';

Answer 1

the 3 and 4 are missing, because you group by MaxMonth which is the same for both. (8 should however be there, it is in my test...) You have to use a little trick: Before cogrouping, you group query 1 by MaxMon and RM. That way you preserve All distinct rows.

q = load "RetentionDATA1";
q1 = group q by 'Month_of_Retention';
q1 = foreach q1 generate q1.'Month_of_Retention' as 'q1.RM',
    sum(q1.'MONTHLY_FEE') as 'q1.SUM',
    toString(toDate(max(q1.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q1.MaxMon';
q1 = group q1 by ( 'q1.MaxMon', 'q1.RM');

Then generate the second query and cogroup. Since we grouped query 1 by MaxMon and RM we get the Rows with RM 3 and 4, too.

q2 = filter q by 'Month_of_Retention' == "1";
q2 = group q2 by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month');
q2 = foreach q2 generate toString(toDate(max(q2.'FIRST_SIGN_UP_sec_epoch')), "M") as 'q2.MaxMon',
    sum(sum(q2.'MONTHLY_FEE')) over([.. 0] partition by all order by ('FIRST_SIGN_UP_Year', 'FIRST_SIGN_UP_Month')) as 'q2.SUM';

result = cogroup q1 by ('q1.MaxMon'), q2 by ('q2.MaxMon');
result = foreach result generate q1.'q1.MaxMon' as 'q1.MaxMon',
    q2.'q2.MaxMon' as 'q2.MaxMon',
    string_to_number(first(q1.'q1.RM')) as 'q1.RM',
    sum(q1.'q1.SUM') as 'q1.SUM',
    sum(q2.'q2.SUM') as 'q2.SUM';
result = order result by 'q1.RM';

Voilá, that should do the trick!