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

asher

Please see my reply!

asher

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

 

 

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. 

asher

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

 

 

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.