Einstein Analytics: Transpose data from rows to columns

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

In this blog, 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