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 Transformer - Table merge with conditional column insertion

Brad Johnson

Hello All.

I have multiple pages, one for each project each containing two tables of data. I want to merge and transform the data into 1 consolidated table.

Table 1 has basic Project Info in Rows. 1 per project page.

Table 2 has Project timeline info in rows and columns. 1 per project page.

What I want is a single table reporting project information, 1 row per project (page) using values from the two tables per project page.

The project info is straight forward (first 5 columns) but I believe I need table transformer to add the timeline columns and populate the cells and that's where I am stuck.

Desired end result

I've had a bit of a go using examples but I'm not an SQL guy and I haven't seen an example that covers this. Any help would be appreciated.

Thanks in advance.

Accepted answers

All comments

Ste Wright

Hi @Brad Johnson

This isn't usually native functionality - but there's a few Apps that does this in Confluence Cloud.

Do you know which App you're using for this?

Ste

Katerina Kovriga {Stiltsoft}

Hi @Brad Johnson ,

I've switched your case back and forth but haven't come with a beautiful solution.

There are several bottlenecks that prevent its implementation:

1. You are on Cloud. You can't nest several macros with bodies inside each other and you can't create vertical tables.

So, if we try to transpose your first table, the result will be a one-row table, but we can't use it further: to transpose it we can use the Table Transformer macro, but see the information below - we can't use the Table Excerpt macro on top to collect this transposed table with meta data later.

So, the first advice is to use standard table view with real header row.

2. As I can see from the result table, the name of the project should be obtained automatically from the Page title. Our Table Excerpt Include macro allows you to pull this meta data (you wrap the source tables into the Table Excerpts macros, collect them via the Table Excerpt Include macros with Page metadata, wrap these Includes in the Table Toolbox macro and then merge them via the Table Transformer macro using this common Page names).

But here comes your second table that needs to be fully reorganized (not just transposed). It could be done, for example, using autonumbering and referring to the every particular date (and we also remember that we need to preserve the Page title to merge our tables later).

To reuse and collect reports from multiple pages really should be very easy, please, see this example.

So I advise you to rethink the concept of the source tables: make them simple and standard. You'll be able to put the structure of the one-row table in the Table Excerpt macro and create a template.

On the master page you'll be able to collect them automatically using the two Table Excerpt Include macros with page metadata (for all the first parts of the table and all the second). Then you wrap these two Includes in the Table Toolbox macro, use the Table Transformer to look up the tables by the Page titles and, for example, the Table Filter macro on top to filter your big table.

Brad Johnson

Hi Katrina,

I can change table 2 in particular as I’m only introducing it now. It is the way it is because on the project page I use it to show a table chart(gantt).

So can I reformat table 2 to achieve both ends. Firstly a gantt on the project page, and secondly my consolidated view?

Katerina Kovriga {Stiltsoft}

Then I can suggest collecting the original second table: once you reuse it for the Gantt Chart and for the second time you wrap it in the Table Filter macro to exclude unwanted rows (with "Implementation") and hide unwanted columns ("Phase").

The result will be a two-row table:

Fri 4-1.png

The first table can be then merged to the second.

I've also passed this case to our developers, if they come up with a better solution, I'll get back here.

Katerina Kovriga {Stiltsoft}

So, here is an option how to modify the original second table to the plain one-row table from our developers: reuse the source table twice via the Table Excerpt Include macro, choose to show Page title in meta data and wrap both Table Excerpt Includes in the Table Toolbox macro.

Fri 5-1.png

Then create the following structure in the Table Toolbox macro: two internal Table Transformer macros (each on top of the Table Excerpt Include macro) and one external Table Transformer macro.

Fri 5-2.png

Here is the SQL query for the first external Transformer (the result table is on the previous screenshot):

SELECT * FROM
(SELECT T1.'Page', T1.'Timeline' + " " + T1.'Phase' AS 'Timeline', T1.'Start Date' FROM T1)
PIVOT (MAX('Start Date') for 'Timeline')

And here is the SQL query for the second internal Transformer:

SELECT * FROM
(SELECT T1.'Page', T1.'Timeline' + " " + T1.'Phase' AS 'Timeline', T1.'End Date' FROM T1)
PIVOT (MAX('End Date') for 'Timeline')

Fri 5-3.png

Now we need to combine the two tables (with Start Dates and End Dates) and rename the columns. Use the following query for the external Transformer:

SELECT T1.'Page',
T1.'Original Planning' AS 'Start Original Planning',
T2.'Original Planning' AS 'End Original Planning',
T1.'Current Planning' AS 'Start Current Planning',
T2.'Current Planning' AS 'End Current Planning'
FROM T1 OUTER JOIN T2 ON T1.'Page' = T2.'Page'

Fri 5-4.png

Now you can reformat your first table horizontally (make it a one-row table), reuse it via the Table Excerpt Include macro with the Page metadata and use one more Table Transformer macro to create a result report.

Welcome!

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

Quick Links

Find more posts tagged with

cloud

confluence-cloud

filter

table

table-filter

excerpt

table-filter-and-charts

table-transformer