1

Assume I have a dataset like such:

Cart Data
----------
oId cId iId $$
1   C1  .   2
1   C1  .   3
1   C2  .   3
2   C1  .   1
3   C2  .   3
3   C3  .   4
4   c4  .   5
4   c4  .   5
5   c2  .   4
6   c1  .   2
6   c3  .   4
...

oId relates to an order cId relates to the item's category iId is the item's id (although isn't filled in because it doesn't matter for the question.) $$ is the price of the item.

This dataset has millions of rows, so I would like to aggregate it before it fills up my SF Analytics data caps.

I would like to do something like:

SELECT 
  oId, cId, sum('$$')
FROM 
  ...
GROUP BY
  oId, cID

This is a very easy task to do in SAQL, but is it possible to do this in the Dataflow? There don't seem to be any nodes that do exactly that sort of thing, and the ways I've tried so far haven't seemed to work.

The method that has gotten me the furthest is using a computeExpression node to generate a value with a windowing function, to sum($$) grouped by (uId, cId). I would also generate the rank grouped/ordered in the same manor, and then filter out rank!=0.

Using a windowing function in a computeExpression node gives me a strange error though:

Unknown IDTOKEN: ___sys_gen_id1

where my computed field is: sum('$$') over ([0 ..] partition by ('id', 'oId') order by 'Id')

... That was probably a lot more information than was necessary. To restate the question, is it possible to aggregate data in SF Analytics Dataflows, and if so how?

0

As of right now (April, 2019), this sort of aggregation is not possible in Analytics Dataflows. There are some pseudo workarounds using multi-value fields, but they are very limited and definitely don't solve all cases. Here is the trailblazer post where I received this answer: https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000009MBP&fId=0D53A00004JPlNU

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.