Pivot/unpivot help Magic ETL

Cbrack
Cbrack Member
in Magic ETL

I have a dataset which looks like this before i pivot it:

image.png

But I would like for it to look like this. Please ignore the fact there are duplicates, for example 2 IHS rows, I can fix that in the etl. I just want to pivot it and I'm struggling on something which seems to be easy. Thank you

image.png

Best Answers

  • david_cunningham
    david_cunningham Coach
    edited May 7 Answer ✓

    @Cbrack here is an example

    image.png

    master_group would be whatever your row-level identifier is. You would need to create a new column for each of the values you want to pivot out.

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @Cbrack if you're saying that you have some rows that are "PTD" and some that are "PTD "?

    If so, you would want to clean that up before doing the pivot.

    You can do that with the following function.

    image.png

    SQUASH_WHITESPACE() works as follows…

    All sequences of whitespace characters, non-breaking spaces, control characters, and null characters (U+000000), are replaced with a single space (' '). Spaces at the beginning and end of the string are dropped.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @Cbrack I just didn’t have anything to group by with the example data I generated to build your desired output. In your case, it could be a constant (Chem Data), or if you wanted to have multiple rows, you could group by something else (for example month or quarter).

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    david_cunningham Coach

    @Cbrack - perhaps I'm misunderstanding you, but to achieve your desired output you should be able to load in the group as your column and then average your qty_percentage in the value. For example.

    image.png

    Or are you saying that you want to turn your rows into columns in your ETL?

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • MichelleH
    MichelleH Coach

    @Cbrack There are a couple options you could use, one within MagicETL and one in Analyzer:

    • The Pivot tile in MagicETL will convert your rows to columns as you're describing. From your screenshot it looks like you only have two columns in your data, so you would need to add a dummy column for the "Select the column(s) that identify a row" drop-down
    • If your desired outcome is to have this data pivoted in a table, then you could leave your dataset as-is and use a Pivot Table card with "Group" in Columns and Qty_Percentage in the Values. If you anticipate needing to add or subtract Groups from your data, this would be the more flexible option.

  • Cbrack
    Cbrack Member

    Thanks, so yes, i would like the end result to be in the ETL, not in analyzer. How would i need to fill out the pivot? I've tried so many different combo's, i'm starting to get confused on what I tried vs. what i haven't tried. Seems simple, but yet, I guess I'm new at it and this is a very basic start. Thanks again

    image.png

  • david_cunningham
    david_cunningham Coach
    edited May 7 Answer ✓

    @Cbrack here is an example

    image.png

    master_group would be whatever your row-level identifier is. You would need to create a new column for each of the values you want to pivot out.

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • Cbrack
    Cbrack Member

    Thank you. That worked. I created a constant and called it Master group, with a text of "Chem Data". believe that's what you meant by the "lowest Level". In my source data, some of the group's contain extra spaces. Like PTD is PTD (2 spaces). is there a way to trim those spaces for the compare?

    image.png

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @Cbrack if you're saying that you have some rows that are "PTD" and some that are "PTD "?

    If so, you would want to clean that up before doing the pivot.

    You can do that with the following function.

    image.png

    SQUASH_WHITESPACE() works as follows…

    All sequences of whitespace characters, non-breaking spaces, control characters, and null characters (U+000000), are replaced with a single space (' '). Spaces at the beginning and end of the string are dropped.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • Cbrack
    Cbrack Member

    Sorry, last question, I understand what the first squash_whitespace does, but what is the purpose of the second Master_group 'group label". Not sure what that is doing. Thank you.

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    @Cbrack I just didn’t have anything to group by with the example data I generated to build your desired output. In your case, it could be a constant (Chem Data), or if you wanted to have multiple rows, you could group by something else (for example month or quarter).

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • Cbrack
    Cbrack Member

    Thank you so much for your help David

Categories