Dynamic Pivot

NathanDorsch
NathanDorsch Member
in Magic ETL

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.

image.png

Tagged:

Answers

  • MichelleH
    MichelleH Coach

    @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.

  • GrantSmith
    GrantSmith Coach

    The only way to dynamically pivot a table would be to utilize a MySQL dataflow and do something like this:

    https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • NathanDorsch
    NathanDorsch Member

    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

    image.png

    Now I've opened up mysql with the dataset…

    image.png

    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
    from salsify_product_requirements_2_columns

    image.png

    Now… I suppose I create another transformation and the example code they provided is

    /Dynamic Reverse Pivot/
    CREATE PROCEDURE Pivot()
    BEGIN

    SET @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?

  • NathanDorsch
    NathanDorsch Member

    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…

  • NathanDorsch
    NathanDorsch Member

    Just bumping this…. can anyone help me? maybe @GrantSmith

    Thanks.

  • MichelleH
    MichelleH Coach

    @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.

  • NathanDorsch
    NathanDorsch Member

    Thanks. I did end up switching over to using the Table transform. Still having issues though…

  • MichelleH
    MichelleH Coach

    @NathanDorsch What issues are you seeing?

  • NathanDorsch
    NathanDorsch Member

    Would you possibly be willing to jump on a call with me?

  • NathanDorsch
    NathanDorsch Member

    @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.

  • NathanDorsch
    NathanDorsch Member

    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: https://domo-support.domo.com/s/article/360042923454?language=en_US

    Thank you.

  • MichelleH
    MichelleH Coach

    @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

  • GrantSmith
    GrantSmith Coach

    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_example

    The 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;
END

    The 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 Pivot

    Finally 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:

    Screenshot 2023-05-06 at 7.44.20 PM.png

    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!**
  • NathanDorsch
    NathanDorsch Member

    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

    image.png

  • NathanDorsch
    NathanDorsch Member

    Actually, it looks like I'm having issues with the third step:

    image.png
    image.png

  • GrantSmith
    GrantSmith Coach
    edited May 2023

    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!**
  • NathanDorsch
    NathanDorsch Member

    that worked! But now the 4th one is still failing:

    image.png

  • GrantSmith
    GrantSmith Coach
    edited May 2023

    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;
END

    Alternatively, I've attached a txt file with the procedure code.

    proc.txt

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • NathanDorsch
    NathanDorsch Member

    @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.

    image.png

  • Laurenamcc
    Laurenamcc Member
    https://community-forums.domo.com/main/discussion/comment/65283#Comment_65283

    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;
    
END

    When 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.

  • art_in_sky
    art_in_sky Contributor
    edited July 24

    Can someone confirm if the above code worked for the dynamic pivot?

    @MichelleH @GrantSmith @NathanDorsch

Categories