How do I Transpose/Pivot DataFlows for Non-Technical Users to Any Columns and Values?
Please see my reply!
Best 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:
Here is the result datasource after the example above has been tranposed/pivoted:
Step 1: Select Input Datasource you want to transpose/pivot
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
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_variables
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_tableOPTIONAL
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:
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
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
Here is a link to the legacy article.
0
Answers
-
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:
Here is the result datasource after the example above has been tranposed/pivoted:
Step 1: Select Input Datasource you want to transpose/pivot
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
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_variables
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_tableOPTIONAL
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:
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
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
Here is a link to the legacy article.
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
- 60 App Studio
- 41 Variables
- 689 Automate
- 177 Apps
- 454 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 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