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 (write the query in one line as shown in the screenshot):

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 (write the query in one line as shown in the screenshot):

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 (write the query in one line as shown in the screenshot):

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 (write the query in one line as shown in the screenshot):

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 and click Next.
  5. Define the parameters if needed.  (ССЫЛКА) 
  6. Save the macro and the page.



SQL query explanation

In the SQL query tab in the macro browser you can find the default query (write the query in one line as shown in the screenshot):

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 (write the query in one line as shown in the screenshot):

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 (write the query in one line as shown in the screenshot):

SELECT
   T1.'Id',
   T1.'Full name',
   T2.'Login',
   T3.'Personal space',
   T3.'Create space',
   3.'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 (write the query in one line as shown in the screenshot):

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 (+ ссылка на FAQ)
  • Worklog settings
  • Decimal separator
  • Thousands separator
  • Decimal places

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 source tables
  • Export to CSV button
  • Export to PDF button
  • Export to Word button

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. (добавить ссылки)




Watch the video to learn how the default presets work:

ВИДЕО С ЮТЮБА