This should be some basic stuff but lack of good documentation on SAQL is making it hard to figure out. I just want to do a simple math like ths-

Avg. Sale Price = Total Sale Price/Qty

The filters used for computing Total Sale Price and Qty are different. For example,

a = load ...
b = filter a by ... //some filters here
b = group b by all;
b = foreach b generate sum('Sale_Price') as 'total_sale_price';

c = filter a by ... //some filters here (different from those used for b)
c = group c by all;
c = foreach c generate count() as 'qty';

Now, I just want to compute b.total_sale_price/c.qty. How can I do that?

  • Just reading some of the documentation ? developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_eql.meta/… -- Seeing how b = Total_sales_price and c = qty... could you not do d = b/c? Or try d = Total_Sales_price/qty?
    – AlphaBravo
    Jun 17 '16 at 12:07
  • b and c are streams (like tables), so what you're saying is b.total_sale_price/c.qty That doesn't work because the fields are not part of same stream. One is from b and another from c Jun 17 '16 at 19:24

You can also do a cogroup on two different data streams. This is also useful for Year over Year calculations. The one challenge with a cogroup is that the fields that you are cogrouping must be the same (Similar to an inner join).

a = load .. 
b = filter a by ..
b = group b by 'Join_Field_1';
b = foreach b generate 'Join_Field_1' as 'Join_Field_1', sum('Sale_Price') as 'total_sale_price';

c = load ..
d = filter c by (different filters)
d = group d by 'Join_Field_2';
d = foreach d generate 'Join_Field_2' as 'Join_Field_2', count() as 'qty';

cg = cogroup b by 'Join_Field_1', d by 'Join_Field_2';
res = foreach cg generate sum(b.'total_sale_price')/sum(d.'qty') as 'sum_avg_sale_price'

Looks like it can be done using pseudo fields. There's probably a better way but this works..

b = foreach b generate sum('Sale_Price') as 'total_sale_price', 0 as 'qty'; 

c = foreach c generate 0 as 'total_sale_price', count() as 'qty'; 

d = union b, c; //pseudo fields allow you to do a union
d = group d by all; 
d = foreach d generate sum('total_sale_price')/sum('qty') as 'avg_sale_price';

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.