Default automatic presets

• Custom Transformation - use cases with advanced SQL queries

Merge Tables preset

Selecting preset

1. Switch the page to the edit mode.

2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.

3. Select the macro and click Edit.

4. In the Presets tab select Merge tables preset and click Next.

5. Define the parameters if needed.

6. Save the macro and the page.

The default SQL query works with no need to change it:

• When the columns have the same column labels (look here to learn what query to use when the column labels differ)

• When the columns are both in the same or in the different orders

SQL query explanation

In the SQL query tab in the macro browser you can find the default query:

SELECT
   * 
FROM
   T*

SELECT * outputs all columns from source tables. Replace the asterisk with a comma separated list of column labels in single quotes to output only these columns in the merged table:

SELECT
   'Name',
   'Age' 
FROM
   T*

FROM T* gets data from all tables within the macro body. Replace the asterisk with the number of the table to use the particular table only:

SELECT
   'Name',
   'Age' 
FROM
   T1

SQL query when the column labels differ

If you want to merge tables with different column labels, use this query example that outputs the column Job from the second table as the column Position:

SELECT
   * 
FROM
   T1 
UNION
SELECT
   *,
   'Job' AS 'Position' 
FROM
   T2

Lookup Tables preset

Selecting preset

1. Switch the page to the edit mode.

2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.

3. Select the macro and click Edit.

4. In the Presets tab select Lookup tables preset.

5. Select the column for matching records and click Next.

6. Define the parameters if needed.

7. Save the macro and the page.

SQL query explanation

In the SQL query tab in the macro browser you can find the default query:

SELECT
   * 
FROM
   T1 
   OUTER JOIN
      T* 
      ON T1.'Name' = T*.'Name'

SELECT * outputs all columns from source tables. Replace the asterisk with a comma separated list of column labels in single quotes to output only these columns in the merged table:

SELECT
   T1.'Name',
   T1.'Age',
   T2.'Certification' 
FROM
   T1 
   OUTER JOIN
      T* 
      ON T1.'Name' = T*.'Name'

OUTER JOIN T* looks up all tables. You can also use other functions:

• OUTER JOIN outputs all rows from all tables even if there are no matches.
• JOIN outputs only those rows, that have matches.
• LEFT JOIN outputs all rows from the first table even if there are no matches.
• RIGHT JOIN outputs all rows from the second table even if there are no matches.

ON T1.'Name' = T*.'Name' is the matching criteria. The rows in the merged table should match on the column Name.

SQL query when the column labels differ

If you want to join tables with different labels of columns for matching, use this query example.  The rows in the joint table should match by the column Id for Table 1, # for Table 2, N for Table 3:

SELECT
   T1.'Id',
   T1.'Full name',
   T2.'Login',
   T3.'Personal space',
   T3.'Create space',
   T3.'Confluence admin',
   T3.'System admin' 
FROM
   T1 
   JOIN
      T2 
      ON T1.'Id' = T2.'#' 
   JOIN
      T3 
      ON T1.'Id' = T3.'N'

SQL query when joining tables with chain look up

If you want to merge two tables by one criterion and the third table by another criterion, use this query. Table 1 matches Table 2 by Login or name, then Table 3 matches them by Level:

SELECT
   T1.'Full name',
   T2.'Level',
   T3.'Personal space',
   T3.'Create space',
   T3.'Confluence admin',
   T3.'System admin' 
FROM
   T1 
   JOIN
      T2 
      ON T1.'Login' = T2.'Name' 
   JOIN
      T3 
      ON T2.'Level' = T3.'Level'

Managing parameters of the macro

Table settings

The following table settings are available in the Table Transformer macro:

• Date format

• Worklog settings

• Decimal separator

• Thousands separator

• Decimal places

• Transpose source tables

• Transpose result tables

To set the settings:

1. Select the macro and click Edit.

2. Switch to the Settings tab.

3. Define settings.

4. Save the macro and the page.

View options

The following view options are available in the Table Transformer macro:

• Show/Hide source data
• Show result as plain text
• Export to CSV button
• Export to PDF button
• Export to Word button

Show result as plain text can be enabled only if the result is displayed as a table with a single cell.

To set the settings:

1. Select the macro and click Edit.
2. Switch to the Options tab.
3. Define settings.
4. Save the macro and the page.

To get more options such as default sorting and row numbering, freezing the first N rows/columns and others wrap the Table Transformer macro in the Table Filter macro.