Return special column headings from an table in another one

[Giuseppe F]

Hello,

i have a case i want to solve with confluence in our company. Unfortunately i can't use the table spreadsheet macro. But perhaps there is a solution that works with the "normal" confluence table, too.

The table we use looks like this:

The use case is:

I want a second table, where confluence "looks up", what the last date (or the last filled column in the fields Date 1, Date 2, Date 3 and so on) is and then return the result with the column heading (and not with the date in the field). The column headings have spaces in their name, if that is important.

So with the example above it would look something like that:

I think i can solve this problem with the table transformer macro. Unfortunately i have no idea how the SQL query would look like. In the best case, i could also insert the other text-colums like "Text1" and "Text2" and so on, but that would only be a bonus.

Can somebody help me with that problem?

[Katerina Kovriga {Stiltsoft} · Accepted solution]

Hi @Giuseppe F ,

The Table Transformer macro works with tables as with a mini SQL database, so to achieve your case we should manually check the dates' columns and assign what information we want to return for every condition.

SELECT T1.'Number', T1.'Text',
CASE WHEN T1.'Date 1' IS NULL THEN "No Dates"
WHEN T1.'Date 3' IS NOT NULL THEN "Date 3"
WHEN T1.'Date 2' IS NOT NULL THEN "Date 2"
WHEN T1.'Date 1' IS NOT NULL THEN "Date 1"
END
AS 'Last Date'
FROM T*

Here we check the 'Date 1' column first - if it is empty, we assume that all other date fields are also empty because we start to fill the row from the 'Date 1' to the 'Date 3' column.

Then we check other dates subsequently: here it is important to start with the last column (that is 'Date 3' for my example) and move back to the first column.

The result will be as following:

 Hope this workaround helps.

[Giuseppe F]

That works perfect. Thanks for the very fast help :)