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:

- 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

- 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

- 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

- 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

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


Page-level ad