## Friday, October 11, 2019

### 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

- 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

- Ke_2 with Source Field = Ke_2_temp and Offset Function = First

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

- 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

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

#### 1 comment:

1. Very useful post!!!
Just had a question if this would work from three columns as below .
If yes then how?

Student Subject Evaluation_result
Smith, John Music 7.0
Smith, John Maths 4.0
Smith, John History 9.0
Smith, John Language 7.0
Smith, John Geography 9.0
Gabriel, Peter Music 2.0
Gabriel, Peter Maths 10.0
Gabriel, Peter History 7.0
Gabriel, Peter Language 4.0
Gabriel, Peter Geography 10.0

Target table

Student Geography History Language Maths Music
Gabriel, Peter 10.0 7.0 4.0 10.0 2.0
Smith, John 9.0 9.0 7.0 4.0 7.0

Thanks !!!!