nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table tranformer for Confluence

Rafi Ahmad_djowdu

Hi Community,

I have a problem statement and thinking how can I use SQL Table Transformer Macro for Confluence.

1. I have three tables coming from three different excerpt include with same columns (some are date column, some text field, some status column).

2. I would want to build 4th table with selected columns and some column to have merged information from three tables -

How can I write query based on Project code (unique field, present on all three tables) to fetch columns like Project Name, Porject Code, Project Status (status field), Highlights, upcoming release - stressing that those three tables are 3 domains for a Project so 4th table will have merged information for highlights and next steps and Status will be Green if all are Green AND Yellow if one of them is Yellow AND RED if one of them is RED.

If one PR# is just present in one table, bring the same information without any transformation.

Domain 1 table				Domain 2 table
PR#	Highlights	Next Steps	Status	PR#	Highlights	Next Steps	Status
PR101	xx	yy	Green	PR101	ii	ll	Green
PR102	AA	BB	Yellow	PR102	mm	nn	Yellow
PR105	DD	EE	Green	PR108	VV	ZZ	Green
	GG	PP	Green	PR109	MN	JK	Green
Domain 3 table				Expected Transformed table
PR#	Highlights	Next Steps	Status	PR#	Highlights	Next Steps	Status
PR101	pp	qq	Green	PR101	xx ii pp	yy ll qq	Green
PR102	ww	gg	Green	PR102	AA mm ww	BB nn gg	Yellow
PR109	VV	ZZ		PR108	VV	ZZ	Green
PR110	WE	WE		PR105	DD	EE	Green
				PR109	VV	ZZ	Green
				PR110	WE	WE

Accepted answers

All comments

Katerina Kovriga {Stiltsoft}

Hi @Rafi Ahmad ,

We can suggest the following steps:

Tue 5-1.png

Here I merge my manually created tables via the Table Transformer macro but you can use the report function of the Table Excerpt Include macro as you reuse your tables via the Table Excerpt macros. As a result, you'll get the following table:

Tue 5-2.png

Tue 5-3.png

Now we use the Table Transformer macro to group our table by the "PR#" column:

Tue 5-4.png

SELECT T1.'PR#',
SUM(T1.'Highlights' + " ") AS 'Highlights',
SUM(T1.'Next Steps' + " ") AS 'Next Steps',
SUM(T1.'Status' + " ") AS 'Status'
FROM T* GROUP BY T1.'PR#'

Tue 5-5.png

The last step is to use another Table Transformer macro to set conditions for your statuses:

Tue 5-6.png

SELECT *,
CASE WHEN T1.'Status' LIKE "%Red%"
THEN "Red"
WHEN T1.'Status' == "Green Green Green" OR T1.'Status' == "Green Green" OR T1.'Status' == "Green"
THEN "Green"
ELSE
"Yellow"
END
AS 'Status' FROM T*

Tue 5-7.png

Hope this helps.

Rafi Ahmad_djowdu

Thanks @Katerina Kovriga _Stiltsoft_

1. When I put this query to fetch group by PR#, I am getting below error. Can you pls suggest

SELECT T1.'PR#', T1.'Project Name',
SUM(T1.'Highlights' + " ") AS 'Highlights',
SUM(T1.'Next Steps' + " ") AS 'Next Steps',
SUM(T1.'Status' + " ") AS 'Status'
FROM T* GROUP BY T1.'PR#'

TypeError: Cannot read properties of undefined (reading 'PR#')

2. I also forgot to mention, if one or all three base tables have some record with no PR# mentioned, I would not like to show them on 4th Table, how to accomodate this exclusion pls (modified the example above)?

3. If status is blank for all of them, show as Blank on 4th table AND if status is blank for one of the table, still follow the same rule as explained above to populate status on 4th table (modified example above).

Rafi Ahmad_djowdu

Katerina Kovriga {Stiltsoft}

1. I see from your screenshot that you are trying to group an unmerged table: there are two tables in a kind of a panel macro. In my example I used a separate Table Transformer macro to merge the table into a big combined one and then used another Transformer to group my entries.

2. We group our entries by PR#, if a blank PR# will appear, it can be hidden (filtered out) with the help of the Table Filter macro (filter for empty cells).

3. What concerns blank statuses, it's always more convenient not to leave your cells empty but to use another status type such as "Undefined". If you want to operate with empty cells, their contents is NULL for the Table Transformer macro. So, you may modify your query with statuses.

Katerina Kovriga {Stiltsoft}

Thought I'd better take screenshots for the blank PR#, Table Filter and reversed filters.

Here are your modified tables with empty PR# cells:

Wed 9-1.png

Here is how your result table will look after the last Table Transformer macro:

Wed 9-2.png

Now we need to set a reversed dropdown filter for "Empty" PR# value with the help of the Table Filter macro:

Wed 9-3.png Wed 9-4.png Wed 9-5.png

Hope this helps.

Katerina Kovriga {Stiltsoft}

And here is a more beautiful solution from our developers:

The internal Table Transformer macro

Wed 10-1.png

SELECT 'PR#',
SUM('Highlights' + " ") AS 'Highlights',
SUM('Next Steps' + " ") AS 'Next Steps',
SUM('Status' + " ") AS 'Status'
FROM (
SELECT * FROM T* WHERE 'PR#' IS NOT NULL
) GROUP BY 'PR#'

Wed 10-2.png

The external Table Transformer macro

Wed 10-3.png

SELECT *,
CASE WHEN T1.'Status' LIKE "%Red%" THEN "Red"
WHEN T1.'Status' LIKE "%Yellow%" THEN "Yellow"
ELSE "Green"
END
AS 'Status' FROM T*

Wed 10-4.png

Hope this helps.

Katerina Kovriga {Stiltsoft}

Hi there,

As this question mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

The macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit mode.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

atl-gruland

marketplace