Aggregating by headers

Use case:

You are to aggregate headers from the Dev-stage 1 to Dev-stage 5 columns by their values and products.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query:

    SELECT 'Product', 'Value', ARRAY('Dev-stage') AS 'Dev-stage'
 FROM ( SELECT * FROM T1 UNPIVOT ('Value' for 'Dev-stage' 
in (T1.'Dev-stage 1',T1.'Dev-stage 2',T1.'Dev-stage 3',T1.'Dev-stage 4',T1.'Dev-stage 5')))
 GROUP BY 'Product', 'Value'
    CODE
  6. Click Next and Save the macro. 
  7. Insert another Table Transformer and wrap the previously created layout with the macro.
  8. Select the macro and click Edit.
  9. In the Presets tab select Custom transformation and click Next.

  10. Enter the following SQL query:

    SELECT * FROM T1
PIVOT (FIRST ('Dev-stage') for 'Value')
    CODE
  11. Click Next. 
  12. Save the macro and the page.

Pivoting tables

Use case:

You need to PIVOT table data in Table Transformer.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query:

    SELECT * FROM T1
PIVOT (MAX('Value') for 'Month')
    CODE
  6. Click Next.
  7. Save the macro and the page.

The aggregation function MAX is applied to an existing column Value. The Month column is the column the data are aggregated by.

The same output can be produced with the Pivot Table macro with versatile functions like MAX, MIN, SUM, etc., specifically designed for easy and accessible table data aggregation.

Unpivoting tables

Use case:

You need to UNPIVOT table data in Table Transformer.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query:

    SELECT * FROM T1
UNPIVOT ('Value' for 'Month' in (T1.'01-2021', T1.'02-2021', T1.'03-2021'))
    CODE
  6. Click Next.
  7. Save the macro and the page.

The UNPIVOT function is applied to the selection above and works with the table generated from it. In the function itself new custom columns - Value and Month - are created (column names can be adjusted). The Value column is filled with the data from the columns listed after in ('01-2021', '02-2021', '03-2021'). The Month column displays the relevant column names listed.

Splitting cell values in a column to different rows

Use case:

You need to split cell values in a column for further rows data aggregation.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query:

    SEARCH / AS @a EX('Skills'->split(",")) / 
        RETURN(@a->'Name' AS 'Name', _ AS 'Skills') FROM T1
    CODE
  6. Click Next. 
  7. Define the table settings and view options if needed. 

  8. Save   the macro and the page.

In this case the underscore in the query (i.e.  _ AS 'Skills') is utilized as a beacon to return the transformed data in the Skills column, while @a -> returns the data corresponding to their row values, hence multipling them as the Skills column data obtain additional rows after being split. 

 

The resulting table can be further utilized with the Pivot Table macro for distinct value aggregation, counting and so on.