How do I Transpose/Pivot DataFlows for Non-Technical Users to Any Columns and Values?

asher
asher Domo Employee
edited March 2023 in Scheduled Reports

Please see my reply!

Best Answer

  • asher
    asher Domo Employee
    Answer ✓










    This article will detail how to use the transpose/pivot dataflow 2.0 which is the enhanced version of my orginal transpose dataflow. Inspired by Spencer and McWayne's groundbreaking datagrid dataflow, this dataflow will allow non-technical users to transpose/pivot much easier than before.

     

    New Features:

    • Built for non-technical users. Unlike the previous version, transpose dataflow 2.0 does not required the user alter/edit complicated stored procedure SQL.
    • The user only needs to edit one simple transform step unlike the previous which required the user to edit 3 complex steps
    • Built to transpose any type of column. The last version was only able to transpose date columns.

     

    Here is an example of a data set that needs to be transposed: Screen Shot 2015-01-22 at 6.38.00 PM.png

     

    Here is the result datasource after the example above has been tranposed/pivoted:

    Screen Shot 2015-01-22 at 6.38.09 PM.png 

     

    Step 1: Select Input Datasource you want to transpose/pivot

    Screen Shot 2015-01-23 at 8.46.27 AM.png

     

     

    Step 2: Copy Transform 1, edit each required line (check Generate Output Table)

     

    non_transpose_columns

    is a list of columns that you do not want to unpivot, but want in the final data source

     

    new_columns

    are the two new pivoted columns in the final datasouce:

    • The first column is the name for the pivoted columns.
    • The second column is the name for the values in the pivoted columns

    excluded_columns

    are the columns you want to exclude from the final datasource.

    • If you do not have columns you wish to exclude, please inlclude a blank column

    Screen Shot 2015-01-23 at 8.56.22 AM.png

     

    source_table

    is the name of the datasource you want to transpose

     

    TIPS

    • Make each column/table is surrounded by backticks (ex: `your column`)
    • Make sure each list is surrounded by single quotes (ex: '`your column1`,  `your column2`')
    • Make sure you have non_transpose_columns, new_columns, excluded_columns, and source_table to lable each of their perspective rows
    • Make sure the Generate Output Table as transpose_variablesScreen Shot 2015-01-23 at 8.54.07 AM.png

    Below is an example of transform 1:

    Select '`Product Number`, `Product Name`, `Product Category`, `Sale Location`' non_transpose_columns
    , '`Tran Date`, `Amount`' new_columns
    , '`Sale Location`' excluded_columns
    , '`product_sales`' source_table

     

     OPTIONAL

    If you want this transform to be customer facing, you can create a Domo Webform or Excel data source using the formatting described above. You will need to add the Domo Websform or Excel as a Datasource and use the following SQL for Transform 1:

    Select * from DomoWebformDatasource

     

    Make sure the webform or excel is laided out in the following format:

    Screen Shot 2015-01-26 at 10.21.46 AM.png

     

     

    Step 3: Copy Transform 2 (Uncheck Generate Output Table)

    CREATE PROCEDURE column_transpose()
    BEGIN
    DECLARE v_new_column VARCHAR(500);
    DECLARE v_excluded_columns VARCHAR(500);
    DECLARE v_non_transpose VARCHAR(500);
    DECLARE v_non_transpose_q VARCHAR(500);
    DECLARE v_source_table VARCHAR(500);
    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE query_col VARCHAR(500);
    DECLARE v_table_col VARCHAR(500);
    DECLARE v_table_query VARCHAR(500);



    DECLARE transpose_cursor CURSOR for SELECT COLUMN_NAME
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA= SCHEMA() AND TABLE_NAME=(select replace(source_table,'`','') from transpose_variables);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;


    SET @new_column = v_new_column;
    SET @excluded_columns=v_excluded_columns;
    SET @non_transpose=v_non_transpose;
    SET @non_transpose_q=v_non_transpose_q;
    SET @source_table=v_source_table;
    SET @table_col=v_table_col;
    SET @table_query=v_table_query;

    -- Sets all the variables from the user defined transform
    select non_transpose_columns from transpose_variables INTO @non_transpose;
    select replace(excluded_columns, '`','\'') as excluded_columns from transpose_variables INTO @excluded_columns;
    select replace(non_transpose_columns, '`','\'') as non_transpose_q from transpose_variables INTO @non_transpose_q;
    select source_table as source_table from transpose_variables INTO @source_table;

    -- create new table based on the columns specified in the first transform
    select replace(concat(non_transpose_columns,',',new_columns, ' varchar(500)'), ',', ' varchar(500),') as non_transpose_columns from transpose_variables into @table_col;
    SET @table_query = CONCAT('CREATE TABLE transpose (',@table_col,');');
    PREPARE stmt FROM @table_query;
    EXECUTE stmt;

    OPEN transpose_cursor;

    get_column: loop

    -- Get the column name value from cursor
    FETCH transpose_cursor INTO query_col;

    -- Set variable to exist loop when cursor finishes looping through the columns
    IF v_finished = 1 THEN LEAVE get_column;
    END IF;

    -- Set the cursor value so it can be inserted at it the new transpose column
    SET @query_col=CONCAT('\'',query_col,'\'');

    -- Set the cusrsor value with back ticks to pull the value in that column
    SET @query_val=CONCAT('`',query_col,'`');

    -- If statement to elimated cursor values that are columns that should be excluded or not transposed (speficied in the 1st transform)
    IF @non_transpose_q not like (concat('\'%',query_col,'%\'')) AND @excluded_columns not like (concat('\'%',query_col,'%\'')) THEN
    SET @ct_sql = CONCAT('INSERT INTO transpose (SELECT ',@non_transpose,', ', @query_col, ', ',@query_val,' FROM ', @source_table ,')');
    PREPARE stmt FROM @ct_sql;
    EXECUTE stmt;
    END IF;
    END LOOP get_column;
    CLOSE transpose_cursor;

    END;

     

    Step 4: Copy Transform 3 (Uncheck Generate Output Table)

    call column_transpose();

     

    Step 5: Create Output Datasource

    Select * from transpose

    By default this tranpose casts every column to a varchar. If you need a column to be another type (int, char, date, etc), you will need to either 1) cast your columns in your output query, or 2) write a beast mode to change the column data type

     

    • Make sure you are querying the tranpose table to populate your final datasource
    • Make sure you give a name to your new Output Datasource

    Screen Shot 2015-01-23 at 9.16.37 AM.png

     

    The 2 most common cast will be date and some number type (decimal, int, float)

    • Date - will require you to use the STR_TO_DATE() function. More documentation can be found here.
    • Decimal/Int - will require you to use the cast(`column` as decimal(20)) New_Column_Name. More documentation can be found here

    Below is an example of casting:

    select `Product Number`
    , `Product Name`
    , `Product Category`
    , STR_TO_DATE(`Tran Date`, '%M %y') `Tran Date`
    , cast(`Amount` as decimal(20)) Amount
    from transpose;

     

    Step 6: Run the Dataflow

    Click Save and Run

    Screen Shot 2015-01-23 at 9.01.26 AM.png

     

    Here is a link to the legacy article. 











Answers

  • asher
    asher Domo Employee
    Answer ✓










    This article will detail how to use the transpose/pivot dataflow 2.0 which is the enhanced version of my orginal transpose dataflow. Inspired by Spencer and McWayne's groundbreaking datagrid dataflow, this dataflow will allow non-technical users to transpose/pivot much easier than before.

     

    New Features:

    • Built for non-technical users. Unlike the previous version, transpose dataflow 2.0 does not required the user alter/edit complicated stored procedure SQL.
    • The user only needs to edit one simple transform step unlike the previous which required the user to edit 3 complex steps
    • Built to transpose any type of column. The last version was only able to transpose date columns.

     

    Here is an example of a data set that needs to be transposed: Screen Shot 2015-01-22 at 6.38.00 PM.png

     

    Here is the result datasource after the example above has been tranposed/pivoted:

    Screen Shot 2015-01-22 at 6.38.09 PM.png 

     

    Step 1: Select Input Datasource you want to transpose/pivot

    Screen Shot 2015-01-23 at 8.46.27 AM.png

     

     

    Step 2: Copy Transform 1, edit each required line (check Generate Output Table)

     

    non_transpose_columns

    is a list of columns that you do not want to unpivot, but want in the final data source

     

    new_columns

    are the two new pivoted columns in the final datasouce:

    • The first column is the name for the pivoted columns.
    • The second column is the name for the values in the pivoted columns

    excluded_columns

    are the columns you want to exclude from the final datasource.

    • If you do not have columns you wish to exclude, please inlclude a blank column

    Screen Shot 2015-01-23 at 8.56.22 AM.png

     

    source_table

    is the name of the datasource you want to transpose

     

    TIPS

    • Make each column/table is surrounded by backticks (ex: `your column`)
    • Make sure each list is surrounded by single quotes (ex: '`your column1`,  `your column2`')
    • Make sure you have non_transpose_columns, new_columns, excluded_columns, and source_table to lable each of their perspective rows
    • Make sure the Generate Output Table as transpose_variablesScreen Shot 2015-01-23 at 8.54.07 AM.png

    Below is an example of transform 1:

    Select '`Product Number`, `Product Name`, `Product Category`, `Sale Location`' non_transpose_columns
    , '`Tran Date`, `Amount`' new_columns
    , '`Sale Location`' excluded_columns
    , '`product_sales`' source_table

     

     OPTIONAL

    If you want this transform to be customer facing, you can create a Domo Webform or Excel data source using the formatting described above. You will need to add the Domo Websform or Excel as a Datasource and use the following SQL for Transform 1:

    Select * from DomoWebformDatasource

     

    Make sure the webform or excel is laided out in the following format:

    Screen Shot 2015-01-26 at 10.21.46 AM.png

     

     

    Step 3: Copy Transform 2 (Uncheck Generate Output Table)

    CREATE PROCEDURE column_transpose()
    BEGIN
    DECLARE v_new_column VARCHAR(500);
    DECLARE v_excluded_columns VARCHAR(500);
    DECLARE v_non_transpose VARCHAR(500);
    DECLARE v_non_transpose_q VARCHAR(500);
    DECLARE v_source_table VARCHAR(500);
    DECLARE v_finished INTEGER DEFAULT 0;
    DECLARE query_col VARCHAR(500);
    DECLARE v_table_col VARCHAR(500);
    DECLARE v_table_query VARCHAR(500);



    DECLARE transpose_cursor CURSOR for SELECT COLUMN_NAME
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA= SCHEMA() AND TABLE_NAME=(select replace(source_table,'`','') from transpose_variables);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;


    SET @new_column = v_new_column;
    SET @excluded_columns=v_excluded_columns;
    SET @non_transpose=v_non_transpose;
    SET @non_transpose_q=v_non_transpose_q;
    SET @source_table=v_source_table;
    SET @table_col=v_table_col;
    SET @table_query=v_table_query;

    -- Sets all the variables from the user defined transform
    select non_transpose_columns from transpose_variables INTO @non_transpose;
    select replace(excluded_columns, '`','\'') as excluded_columns from transpose_variables INTO @excluded_columns;
    select replace(non_transpose_columns, '`','\'') as non_transpose_q from transpose_variables INTO @non_transpose_q;
    select source_table as source_table from transpose_variables INTO @source_table;

    -- create new table based on the columns specified in the first transform
    select replace(concat(non_transpose_columns,',',new_columns, ' varchar(500)'), ',', ' varchar(500),') as non_transpose_columns from transpose_variables into @table_col;
    SET @table_query = CONCAT('CREATE TABLE transpose (',@table_col,');');
    PREPARE stmt FROM @table_query;
    EXECUTE stmt;

    OPEN transpose_cursor;

    get_column: loop

    -- Get the column name value from cursor
    FETCH transpose_cursor INTO query_col;

    -- Set variable to exist loop when cursor finishes looping through the columns
    IF v_finished = 1 THEN LEAVE get_column;
    END IF;

    -- Set the cursor value so it can be inserted at it the new transpose column
    SET @query_col=CONCAT('\'',query_col,'\'');

    -- Set the cusrsor value with back ticks to pull the value in that column
    SET @query_val=CONCAT('`',query_col,'`');

    -- If statement to elimated cursor values that are columns that should be excluded or not transposed (speficied in the 1st transform)
    IF @non_transpose_q not like (concat('\'%',query_col,'%\'')) AND @excluded_columns not like (concat('\'%',query_col,'%\'')) THEN
    SET @ct_sql = CONCAT('INSERT INTO transpose (SELECT ',@non_transpose,', ', @query_col, ', ',@query_val,' FROM ', @source_table ,')');
    PREPARE stmt FROM @ct_sql;
    EXECUTE stmt;
    END IF;
    END LOOP get_column;
    CLOSE transpose_cursor;

    END;

     

    Step 4: Copy Transform 3 (Uncheck Generate Output Table)

    call column_transpose();

     

    Step 5: Create Output Datasource

    Select * from transpose

    By default this tranpose casts every column to a varchar. If you need a column to be another type (int, char, date, etc), you will need to either 1) cast your columns in your output query, or 2) write a beast mode to change the column data type

     

    • Make sure you are querying the tranpose table to populate your final datasource
    • Make sure you give a name to your new Output Datasource

    Screen Shot 2015-01-23 at 9.16.37 AM.png

     

    The 2 most common cast will be date and some number type (decimal, int, float)

    • Date - will require you to use the STR_TO_DATE() function. More documentation can be found here.
    • Decimal/Int - will require you to use the cast(`column` as decimal(20)) New_Column_Name. More documentation can be found here

    Below is an example of casting:

    select `Product Number`
    , `Product Name`
    , `Product Category`
    , STR_TO_DATE(`Tran Date`, '%M %y') `Tran Date`
    , cast(`Amount` as decimal(20)) Amount
    from transpose;

     

    Step 6: Run the Dataflow

    Click Save and Run

    Screen Shot 2015-01-23 at 9.01.26 AM.png

     

    Here is a link to the legacy article. 











This discussion has been closed.