How to merge tables(more than two) in confluence table transformer?

[Oujy_lala]

Hihi,

I had two tables as below(T1&T2), the date of both table is shown with "MMMYY,SPRINTxxx"(such as JAN25,Sprint23:06/01/2025), now i want the result display with Date MMMYY(such as Jan25) with the data of each month accordingly, order by MMMYY. i had tried the SQL below via using table transfomer in confluence, but it only show the data of T1 and Mar25 of T2, and did not show the DATA of Apr25 of T2, also the order is not sequence by data. Can somebody please help on it? Thank you so much.

SELECT

SUBSTR(T3.'Labels',1,5),

sum(T3.'Count') AS "PJ Done",

SUM(T4.'Count') AS "PJ On-going"

FROM T3

FULL OUTER JOIN T4

ON SUBSTR(T3.'Labels',1,5)=SUBSTR(T4.'Labels',1,5)

where SUBSTR(T3.'Labels',1,5)!="Total"

GROUP BY SUBSTR(T3.'Labels',1,5)

ORDER BY CAST('20'||SUBSTR('Labels',4,2) AS INTEGER), CASE SUBSTR('Labels',1,3)

WHEN "Jan" THEN 1

WHEN "Feb" THEN 2

WHEN "Mar" THEN 3

WHEN "Apr" THEN 4

WHEN "May" THEN 5

WHEN "Jun" THEN 6

WHEN "Jul" THEN 7

WHEN "Aug" THEN 8

WHEN "Sep" THEN 9

WHEN "Oct" THEN 10

WHEN "Nov" THEN 11

WHEN "Dec" THEN 12

END

[Melbrin NGOUILOU_Andemex · Accepted solution]

Hi there, thanks for sharing the details and the screenshot — that really helped clarify the issue.

It looks like the main problem comes from the WHERE clause and how the labels are referenced. Using SUBSTR(T3.'Labels',1,5)!="Total" may exclude rows where T3 is null, which could be why Apr25 from T2 isn't showing up.

Here’s a revised version of the SQL, which keeps the filter for "Total" but avoids dropping months from T2:

SELECT
COALESCE(SUBSTR(T3.'Labels',1,5), SUBSTR(T4.'Labels',1,5)) AS "Date",
SUM(T3.'Count') AS "Done",
SUM(T4.'Count') AS "In-progress"
FROM T3
FULL OUTER JOIN T4
ON SUBSTR(T3.'Labels',1,5) = SUBSTR(T4.'Labels',1,5)
WHERE COALESCE(SUBSTR(T3.'Labels',1,5), SUBSTR(T4.'Labels',1,5)) != "Total"
GROUP BY COALESCE(SUBSTR(T3.'Labels',1,5), SUBSTR(T4.'Labels',1,5))
ORDER BY 
CAST('20' || SUBSTR(COALESCE(T3.'Labels', T4.'Labels'), 4, 2) AS INTEGER),
CASE SUBSTR(COALESCE(T3.'Labels', T4.'Labels'), 1, 3)
WHEN 'Jan' THEN 1
WHEN 'Feb' THEN 2
WHEN 'Mar' THEN 3
WHEN 'Apr' THEN 4
WHEN 'May' THEN 5
WHEN 'Jun' THEN 6
WHEN 'Jul' THEN 7
WHEN 'Aug' THEN 8
WHEN 'Sep' THEN 9
WHEN 'Oct' THEN 10
WHEN 'Nov' THEN 11
WHEN 'Dec' THEN 12
END

Could you test this and see if it works as expected? Let us know if you still face any issues!

[Oujy_lala]

Hi @Melbrin NGOUILOU_Andemex , thank you! I had tested the revised SQL, it works, now i can see the data of Apr also.

But there is another problem is that the order is not by 'Date', it shown like Feb25, Apr25, Jan25, Mar25, something like this.   looks like the SQL below did not effective...any idea?

ORDER BY

CAST('20' || SUBSTR(COALESCE(T3.'Labels', T4.'Labels'), 4, 2) AS INTEGER),

CASE SUBSTR(COALESCE(T3.'Labels', T4.'Labels'), 1, 3)

WHEN 'Jan' THEN 1

WHEN 'Feb' THEN 2

WHEN 'Mar' THEN 3

WHEN 'Apr' THEN 4

WHEN 'May' THEN 5

WHEN 'Jun' THEN 6

WHEN 'Jul' THEN 7

WHEN 'Aug' THEN 8

WHEN 'Sep' THEN 9

WHEN 'Oct' THEN 10

WHEN 'Nov' THEN 11

WHEN 'Dec' THEN 12

[Daniel Berežnoi]

Hi @Oujy_lala!

There may be some conflict between the ORDER BY and GROUP BY portions of the query. To deal with this, you can try the following query:

SELECT * from (SELECT
COALESCE(SUBSTRING(T3.'Labels',1,5), SUBSTRING(T4.'Labels',1,5)) AS 'Date',
SUM(T3.'Count') AS 'Done',
SUM(T4.'Count') AS 'In-progress'
FROM T3
FULL OUTER JOIN T4
ON SUBSTRING(T3.'Labels',1,5) = SUBSTRING(T4.'Labels',1,5)
WHERE COALESCE(SUBSTRING(T3.'Labels',1,5), SUBSTRING(T4.'Labels',1,5)) != "Total"
GROUP BY COALESCE(SUBSTRING(T3.'Labels',1,5), SUBSTRING(T4.'Labels',1,5)))
ORDER BY 
CASE UCASE(SUBSTRING('Date', 1, 3))
WHEN "JAN" THEN 1
WHEN "FEB" THEN 2
WHEN "MAR" THEN 3
WHEN "APR" THEN 4
WHEN "MAY" THEN 5
WHEN "JUN" THEN 6
WHEN "JUL" THEN 7
WHEN "AUG" THEN 8
WHEN "SEP" THEN 9
WHEN "OCT" THEN 10
WHEN "NOV" THEN 11
WHEN "DEC" THEN 12
END,
SUBSTRING('Date',4,2)

A couple of notes:

• Changed the quotes around the month names. To denote string values in Table Transformer, double quotes should be used.
• Converted month abbreviations to uppercase and used the UCASE function to ensure case-insensitive comparison.
• Partitioned the query: the subquery performs grouping, and the outer query handles sorting.
• Adjusted the sorting logic so that months are prioritized over the numeric portion of the date.

Let me know if this works for you

[Oujy_lala]

Hi @Daniel Berežnoi 

Thank you so much, now it works !!!

Thank you too @Melbrin NGOUILOU_Andemex  !!

Really appreciate your help! Have a nice weekend !

[Daniel Berežnoi]

Hi!

It's Daniel from the Stiltsoft team, the developers of the Table Filter, Charts & Spreadsheets. @Melbrin NGOUILOU_Andemex answer looks spot-on. Thanks a ton for jumping in to help out, Melbrin — we really appreciate it!

Don't hesitate to reach out to us through our support portal with these kinds of questions — we are happy to help!

Best,

Daniel

[Melbrin NGOUILOU_Andemex]

Thanks Daniel !

Melbrin