Page History

Table Transformer is one of the macros bundled in the Table Filter and Charts for Confluence app. The macro allows you to merge and transform all kinds of tables using both automatic presets and advanced SQL queries.

Table of Contents

Sample table #1 - Merge tables preset

Two tables with the same set of columns are merged using the Merge tables preset.

Switch to the page editor and explore the Table Transformer macro configuration.

Table Transformer

dateFormat	m/d/yy
export-word	false
show-source	false
export-csv	false
id	1554112380644_1305326515
worklog	5\|8\|w d h m\|w d h m
separator	.
export-pdf	false
sql	SELECT * FROM T*

Employee	Age	Experience, years	Position
Andy Miller	25	3	Middle front-end developer
Ashley Stone	23	1.2	Junior front-end developer
Jennifer Johnson	24	3.5	Middle front-end developer

Employee	Experience, years	Position	Age
Oliver Black	4	Middle front-end developer	27
Peter Jacobs	1.5	Junior front-end developer	24
Thomas Powell	6	Senior front-end developer	30

Sample table #2 - Lookup tables preset

Two tables with one common column are merged using the Lookup tables preset.

Switch to the page editor and explore the Table Transformer macro configuration.

Table Transformer

dateFormat	m/d/yy
export-word	false
show-source	false
export-csv	false
id	1554112854151_-1579224685
worklog	5\|8\|w d h m\|w d h m
join-column	Name
separator	.
export-pdf	false
sql	SELECT * FROM T1 OUTER JOIN T* ON T1.'Name' = T*.'Name'

Name	Age	Experience, years	Position
Andy Miller	25	3	Middle front-end developer
Ashley Stone	23	1.2	Junior front-end developer
Jennifer Johnson	24	3.5	Middle front-end developer
Oliver Black	27	4	Middle front-end developer
Peter Jacobs	24	1.5	Junior front-end developer
Thomas Powell	30	6	Senior front-end developer

Name

JavaScript

Java

Python

Certification

Andy Miller

Status

colour	Green
title	Certified

Ashley Stone

Status

title	Uncertified

Jennifer Johnson

Status

colour	Green
title	Certified

Oliver Black

Status

colour	Green
title	Certified

Peter Jacobs

Status

title	Uncertified

Thomas Powell

Status

title	Uncertified

Sample table #3 - Custom transformation

In the SQL query tab you can add any custom query to perform complex calculations and any kind of table transformation.

You can find a list of all the available SQL functions here and more examples of custom transformation here.

In this example, we use the following query to calculate and add the Priority column based on some conditions:

Code Block
SELECT *, CASE WHEN 'Subtotal' < 1000 THEN "LOW" WHEN 'Subtotal' >= 1000 AND 'Subtotal' < 2000 THEN "MEDIUM" ELSE "HIGH" END AS 'Priority' FROM T1

Switch to the page editor and explore the Table Transformer macro configuration.

Table Transformer

dateFormat	m/d/yy
export-word	false
show-source	false
export-csv	false
id	1554112767935_676640147
worklog	5\|8\|w d h m\|w d h m
separator	.
export-pdf	false
sql	SELECT *, CASE WHEN 'Subtotal' < 1000 THEN "LOW" WHEN 'Subtotal' >= 1000 AND 'Subtotal' < 2000 THEN "MEDIUM" ELSE "HIGH" END AS 'Priority' FROM T1

Order date	Store	Items	Subtotal
6/30/2020	Store #3	Date stamp	$1040.00
6/29/2020	Store #1	Date stamp	$784.00
6/28/2020	Store #2	Plastic folder with visiting card holding sheet	$2,619.00
6/27/2020	Store #3	ID card holder	$3,128.00
6/26/2020	Store #3	Letter opener	$3,792.00
6/25/2020	Store #2	Notice board size 3x2fit & 5x3fit	$320.00
6/24/2020	Store #3	Scissors (big)	$78.00
6/24/2020	Store #2	File separator A-4 size	$2,254.00
6/23/2020	Store #2	Stapler 10-D	$1,472.00
6/22/2020	Store #2	Acknowledgement book	$3,520.00
6/21/2020	Store #3	File separator A-4 size	$3,441.00
6/20/2020	Store #1	A4 size plastic file	$3,762.00
6/19/2020	Store #2	A4 size plastic file	$3,526.00
6/18/2020	Store #2	Date stamp	$3,078.00
6/17/2020	Store #1	Gel pen	$3,268.00
6/16/2020	Store #3	Magnetic white board duster	$1,035.00
6/15/2020	Store #2	Desk calculator	$2,964.00
6/14/2020	Store #1	Letter opener	$3,045.00
6/13/2020	Store #2	Magnetic white board duster	$1,581.00
6/12/2020	Store #3	Visiting card holder	$874.00
6/11/2020	Store #2	Printing of envelope A-4 size brown	$182.00
6/11/2020	Store #1	Office tray ( in & out )	$5,000.00
6/10/2020	Store #3	Brown paper sheet	$2,976.00
6/9/2020	Store #1	Bubble sheet	$3,589.00
6/8/2020	Store #2	Cello tape 1"	$2,160.00
6/7/2020	Store #3	Non dust chalk	$3,772.00
6/6/2020	Store #1	Eraser	$1,176.00
6/5/2020	Store #1	Key ring	$495.00
6/5/2020	Store #2	Highlighter (yellow,green,orange)	$2,726.00
6/4/2020	Store #1	Brown paper sheet	$280.00
6/3/2020	Store #1	Cello tape 1"	$980.00
6/3/2020	Store #2	Scissors (big)	$2,278.00
6/2/2020	Store #3	Tape dispenser	$264.00
6/2/2020	Store #3	Printing of invitation cards	$561.00
6/2/2020	Store #1	Post it 2 x 3	$600.00
6/2/2020	Store #2	A4 size writing pad spiral	$624.00
6/2/2020	Store #3	Plastic folder A4 size-	$1,740.00
6/2/2020	Store #3	Sticker Sign here	$4,150.00
6/1/2020	Store #1	Paper cutter stand	$1,825.00

Priority

Status

colour	Green
title	LOW

Status

colour	Yellow
title	MEDIUM

Status

colour	Red
title	HIGH

Space shortcuts

Page tree

Versions Compared

Old Version 4

New Version 5

Key

Sample table #1 - Merge tables preset

Sample table #2 - Lookup tables preset

Sample table #3 - Custom transformation