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

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!