Complex Pivot

har_d_har1

How can I use Magic ETL to convert a table from

to

without using scripting? I have to remove all scripting tiles from my ETLs. I'm assuming some combination of RANK & WINDOW, PIVOT and GROUP BY, but am running out of talent.

ArborRose

My incoming dataset:

My flow looks like this:

The two outputs (Movie 1, Movie 2) at the bottom are for you to debug and see what is happening.

Next I create a Rank using Row_Number. The order is by Role, Name. The partition is based on Team.

Then I concatenate the Role and Rank to get the labels we want. This is going to result in something slightly different than what you show. I'll explain.

Here's where those labels differ…

We are going to pivot based on the labels. The leader of the Team is an R1. The subordinates are R2. The row number is going to only have R1 1 for the leader. The rest will be R2 1, R2 2, R2 3, and R2 4.

finally, we limit the columns to those labels.

To get the result:

ArborRose

The debug tiles I used, help me "see" what is happening with the data and their values. This helps me identify the needs for the pivot.

Note in the Movie 2 debug below, we see the Role_Rank that will become our new column headers.

ArborRose

Create a Pivot. Use the "Pivot" tile to pivot the Role column into new columns. Then group and aggregate by using the "Group By" tile to group by Team and concatenate the names for each role.

{Urg…apologies, my solution below is a bit different than I described. We don't group by.}

ArborRose

My incoming dataset:

My flow looks like this:

The two outputs (Movie 1, Movie 2) at the bottom are for you to debug and see what is happening.

Next I create a Rank using Row_Number. The order is by Role, Name. The partition is based on Team.

Then I concatenate the Role and Rank to get the labels we want. This is going to result in something slightly different than what you show. I'll explain.

Here's where those labels differ…

We are going to pivot based on the labels. The leader of the Team is an R1. The subordinates are R2. The row number is going to only have R1 1 for the leader. The rest will be R2 1, R2 2, R2 3, and R2 4.

finally, we limit the columns to those labels.

To get the result:

ArborRose

The debug tiles I used, help me "see" what is happening with the data and their values. This helps me identify the needs for the pivot.

Note in the Movie 2 debug below, we see the Role_Rank that will become our new column headers.