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.

[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:

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.

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.

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')

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'

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.

[Brad Johnson]

Thank you so much for your effort Katerina. I will try it and report back.

[Brad Johnson]

Hi Katerina. I am a bit stuck.

My original table is as it was. I can't easily change that and I'm worried the end of your instructions were saying to change it to a horizontal table instead of vertical.

I have followed your instructions and got the 'transformed' table via the 2 Table Transformers using Table Excerpt Includes.

I currently use a page properties report to report on all the instances of table 1. 

I don't know how to add the 'transformed' table into each row of the page properties report.

Or maybe I could create a new vertical table on each page that combines the vertical table 1 and the 'transformed' table such that the 'transformed' table columns become rows (using transform result table?) in the new vertical table with a Page properties macro around it to feed the page properties report.

[Katerina Kovriga _Stiltsoft_]

Your bottleneck comes from Confluence Cloud - you can't nest macros in Cloud (I mean wrap them inside each other).

So, because of this our Table Toolbox macro was developed (for Server/Data Center you may wrap macros directly).

It comes so that if your first table is wrapped into the Page Properties macro, you won't be able to wrap it into other macros.

The workaround may be to reuse this table twice: wrap it in the Table Excerpt macro and place one Table Excerpt Include macro inside the Page Properties macro (the Table Excerpt Include macro is a bodiless macro, so it will be allowed).

The second Table Excerpt Include macro will be included in the Table Toolbox macro. But again to use the Page Title correctly it should be set horizontally (in one row). You won't be able to reuse vertical table, transpose it and reuse it again with page meta data on Cloud.

[Brad Johnson]

EDIT: You can ignore the below. I simply moved my Table Toolbox from the project page to the project report page, changed the scope of the Table Excerpt Includes and it worked!

Well, I need to tweak the data a bit more now but it certainly looks like it worked. And I didn't touch my Table 1. Its still vertical which is great since that's what the users edit.

Hi Katerina.

I'll just play this back in my own words to make sure I understand.

I now have a Table Toolbox (including the complex table transformation you specified above) on my project page which produces a single row table with exactly the information I want in the parent 'report' page (still using my original Table 1 formatted vertically). But I can't 'export' this table in any way due to macro nesting limitations in Confluence Cloud. I can't wrap table toolbox in either a Table Excerpt or Page Properties either external to it or internal to it.

So, as per your directions above, I need to reformat my table 1 from vertical to horizontal, Table Excerpt it and then use Table Transformer on the parent report page to merge the project page data tables. But don't I have the same issue where I can't 'export' the complex table transformed table 2 from the project page for the parent page to be able to use it? Oh, or does this need to be put on the parent reporting page as part of the overall report construction?

[Katerina Kovriga _Stiltsoft_]

Due to the time zones difference I've seen the answer with the update. )

Glad that the case has worked for you. It's really a bit complicated especially for Cloud.)

[Katerina Kovriga _Stiltsoft_]

Hi @Brad Johnson ,

We've just released a new version of the Table Filter and Charts for Confluence app with the Table Excerpt macro inside the Table Toolbox macro.

Now you'll be able to create macro' sequences (for example, "source table <- Table Transformer <- Table Filter <- Pivot Table") and reuse the result table.

Hope it will help your cases as well.

[Brad Johnson]

Thank you for letting me know Katerina.

I love Stiltsoft!

[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