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?