Dynamic Pivot
I have a table with a combination of fields and a target value. I want to concat all the fields (Retailer, Field, Target, Strategic Priority) and pivot the table so they are all columns with their respective Target Value underneath.
I know how to do this with the Pivot option in which I manually create a new column for each combination. However, is there a way to have this automatically create new columns? My concern (beyond a whole bunch of manual input - I currently have 67 rows) is there will be rows (with new field combinations) added to this dataset and they'll be excluded unless I recognize and manually add them in.
Below is a screenshot of the data I'm working with for context.
Answers
-
@NathanDorsch Can you share a little bit more about what you want to do with the data once it's pivoted? In cases like this, I generally find the most success in leaving the dataset as-is and doing the actual pivoting in the cards.
0 -
The only way to dynamically pivot a table would be to utilize a MySQL dataflow and do something like this:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
I found this article which provides essentially the same solution and walks me how to do this via mysql. I've never used mysql in here before though… and the directions are a bit confusing to me. Can you advise what I'm supposed to do? I first created an output dataset which reduces the columns down to just two columns: Retailer-Field-Target-Priority and Target Value
Now I've opened up mysql with the dataset…
When I click on Add Transform, I get two options: Table and SQL. I went with SQL and created the first transformation with SELECT concat('
',
Retailer-Field-Taget-Priority,'
') as RFTP,Target Value
fromsalsify_product_requirements_2_columns
Now… I suppose I create another transformation and the example code they provided is
/Dynamic Reverse Pivot/
CREATE PROCEDURE Pivot()
BEGINSET @cols =
(SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(p.Account = ''', Account, ''', p.Amount, NULL)) AS ', Account)
) AS Base
FROM 1st_transform);SET @sql = (SELECT CONCAT('CREATE TABLE Pivot AS SELECT p.Region , p.Month, p.Year, p.Vertical, ', @cols, '
FROM 1st_transform p
GROUP BY p.Region, p. Month, p.Year, p.Vertical'));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END/* CAll Store Procedure */
CALL Pivot()
Can you advise on how to adjust this code to fit my data? I'm a little lost here…
And then what is the next step?
0 -
Could somone possibly jump on a call to walk me through this? I feel like this is something that should probably only take a few mins for someone who knows what they're doing. But it take me hours of headaches until I give up…
0 -
Just bumping this…. can anyone help me? maybe @GrantSmith
Thanks.
0 -
@NathanDorsch Your first transform with the SELECT statement should be in a Table transform, not SQL. Whenever you need to create a subquery, you need to use a Table transform in order to name the table and reference it in subsequent transforms. SQL transforms are for actions that don't require an output table.
0 -
Thanks. I did end up switching over to using the Table transform. Still having issues though…
0 -
@NathanDorsch What issues are you seeing?
0 -
Would you possibly be willing to jump on a call with me?
0 -
@MichelleH I ended up taking it in many different directions and honestly can't tell you what is happening, I just keep getting errors saying "whoops, something went wrong" or other things… It'd probably be a lot easier if I can just walk you through it and hoepfully you'll discover what I'm doing wrong pretty quickly.
0 -
Well you can check my availability here. If you or anyone can schedule a quick call with me to walk me through this, I'd be very grateful. Or if someone knows the person who wrote this article on how do this and can point me to him/her:
Thank you.
0 -
@NathanDorsch Unfortunately I'm not able to get on a call. If you post some screenshots of where the errors are occurring we may be able to help identify the issue
0 -
Hi @NathanDorsch, I was able to get some time this weekend to dig in a bit deeper to your issue.
You'll want to use the following code to do a dynamic pivot using a MySQL dataflow. Replace the nathandortch_pivot_table_example in your code to the name of your input dataset name.
The first transform you'll have is a table transform to format your values if necessary. Make sure it's called preformat
select Retailer, Field, Target, Strategic Priority, Target Value
from nathandortch_pivot_table_exampleThe second transform is a SQL transform to clear out the procedure if it already exists:
DROP PROCEDURE IF EXISTS Pivot;
The third transform is another SQL transform to create you dynamic pivot table. This was built off your example dataset.
/Dynamic Reverse Pivot/
CREATE PROCEDURE Pivot()
BEGIN SET @cols =
(SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(p.Target = ''', Target, ''', p.Target Value, NULL)) AS ', Target)
) AS Base
FROM preformat); SET @sql = (SELECT CONCAT('CREATE TABLE Pivot AS SELECT p.Retailer , p.Field, p.Strategic Priority,', @cols, '
FROM preformat p
GROUP BY p.Retailer , p.Field, p.Strategic Priority'));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; ENDThe next transform is another SQL transform to call the procedure and create the table with the data
CALL Pivot();
The next transform is a Table transform to pull the data into a domo data table. This should be called
transform_data_1
select *
from PivotFinally under output dataset pull the data from the
transform_data_1
table.select * from transform_data_1
You should then have something that ends up looking like this:
Hope this help clear things up for you.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thanks @GrantSmith I'm having issues with the 4th step: Call Pivot(); I get an invalid error message: Procedure dataflow_schema.Pivot does not exist
0 -
Actually, it looks like I'm having issues with the third step:
0 -
Looks like my copy and paste was bitten by the forum formatting.
Remove the first line and it should work.
/Dynamic Reverse Pivot/
Alternatively you can change it to what it should be
/*Dynamic Reverse Pivot*/
the /*…*/ just represents a commend to add some contextual information but doesn't do anything to the actual code.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
that worked! But now the 4th one is still failing:
0 -
More auto-formatting issues. Try this version for your procedure definition.
/*Dynamic Reverse Pivot*/
CREATE PROCEDURE Pivot()
BEGIN SET @cols =
(SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(p.`Target` = ''', `Target`, ''', p.`Target Value`, NULL)) AS ', `Target`)
) AS Base
FROM `preformat`); SET @sql = (SELECT CONCAT('CREATE TABLE Pivot AS SELECT p.`Retailer`, p.`Field`, p.`Strategic Priority`,', @cols, '
FROM preformat p
GROUP BY p.`Retailer`, p.`Field`, p.`Strategic Priority`'));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; ENDAlternatively, I've attached a txt file with the procedure code.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith I got all the transformations to run. However, when I look at the resulting dataset, it doesn't have the data pivoted like I wanted… it looks the same as the input dataset.
My whole objective is to convert a concatenated value of Retailer-Field-Strategic Priority-Target set up as columns - and just one row of the Target Value values.
0 -
I am new to using MySQL. Trying to follow along but am running into similar errors as seen in this comment. I have a similar situation but do not need to combine any values like OP does. @NathanDorsch did you get this figured out or @GrantSmith would you possibly be able to help?
I have a dataset that looks like this:
Id
FieldName
1
Name1
2
Name2
3
Name3
4
Name4
5
Name5
6
Name6
7
Name7
8
Name8
That I would like to look like this, the FieldNames will change over time so the pivot needs to be dynamic:
Name1
Name2
Name3
Name4
Name5
Name6
Name7
Name8
1
2
3
4
5
6
7
8
My third SQL Transform is running successfully:
CREATE PROCEDURE Pivot()
BEGIN
SET @cols =
(SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(p.`FieldName` = ''', `FieldName`, ''', p.`Id`, NULL)) AS ', `FieldName`)
) AS Base
FROM preformat);
SET @sql = (SELECT CONCAT('CREATE TABLE Pivot AS SELECT *,', @cols, '
FROM preformat p'));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ENDWhen I run the Transform
CALL Pivot();
I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Type,MAX(IF(p.`FieldName` = 'Description of Accident', p.`Id`, NULL)) AS Descrip' at line 1
Any thoughts on what I am doing wrong? Thanks in advance for looking at this.
0 -
Can someone confirm if the above code worked for the dynamic pivot?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive