Pages

Friday, October 11, 2019

Einstein Analytics: Transpose from row to column

You know, in life you face multiple weird things, the same when you work as an Einstein Analytics consultant. In the previous blog, we share how to transpose data from multiple columns to rows.



In this blog, it is the other way round, the requirement is the other way rounds.

Background: we need to show child records on the parent level, the number of rows in the table should follow the number of parents, and the good news is, there is a limit of children for a parent, and for this case, the max child is 5.

Solution: use a lot of computeRelative transformation nodes.



Let us go through each computeRelative nodes:

Node-1
- Partition By = Country
- Order By = City ascending

Add 3 fields here:
- IsFirst with SAQL: case when current(City)==first(City) then "Yes" else "No" end
- Ke_1 with Source Field = City and Offset Function = First
- Ke_2_temp with SAQL: case when previous(City)==first(City) then current(City) else "" end


Node-2
- Partition By = Country
- Order By = Ke_2_temp descending

Add a field here:
- Ke_2 with Source Field = Ke_2_temp and Offset Function = First


Node-3
- Partition By = Country
- Order By = City ascending

Add a field here:
- Ke_3_temp with SAQL: case when current(City)==Ke_2 then next(City) else "" end


Node-4
- Partition By = Country
- Order By = Ke_3_temp descending

Add a field here:
Ke_3 with Source Field = Ke_3_temp and Offset Function = First


Repeat above until Node-8, only field in yellow highlight will be used, and the one end with _temp will be drop, we just use it as helpers.

Use filter node to drop all rows without IsFirst == "Yes", and slide node to drop all fields except Country and the ones in yellow.

Here is the source


Here is the result






No comments:

Post a Comment

Page-level ad