Pivot/unpivot help Magic ETL
I have a dataset which looks like this before i pivot it:
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
Best Answers
-
@Cbrack here is an example
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.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
@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.
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! ✔️**0 -
@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! ✔️**1
Answers
-
@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.
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! ✔️**1 -
@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.
0 -
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
0 -
@Cbrack here is an example
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.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
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?
0 -
@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.
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! ✔️**0 -
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.
0 -
@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! ✔️**1 -
Thank you so much for your help David
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 680 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive