I am new to SAQL and am trying to join two datasets (let's call them q and r) using cogroup. I have 4 columns in each table (let's call these A, B, C and D). Columns A - C are the same in both datasets but column D is different, so I want to use an inner join against columns A - C to produce a result that will have the columns A, B, C, D, E (renamed from D in the 2nd dataset). However, when I try to do this I am getting a number of errors.
So far I have tried the following:
q = cogroup q by 'A', 'B', 'C', r by 'A', 'B', 'C';
q = cogroup (q by 'A', r by 'A') and cogroup (q.'B', r.'B') and cogroup (q.'C', r.'C');
q = cogroup (q by 'A', r by 'A'), cogroup (q.'B', r.'B'), cogroup (q.'C', r.'C');
q = cogroup q by 'A', r by 'A' and q by 'B', r by 'B' and q by 'C', r by 'C';
None of these give me the desired a result and I get errors (not all the same errors) for each of these.
In each instance this has been followed by:
q = foreach q generate q.'A' as 'A', q.'B' as 'B', q.'C' as 'C', sum(q.'D') as 'D', sum(r.'D') as 'E';
I have struggled to find any examples online of where multiple fields are used for the join, can anyone provide any solution to this please?