Writing a CASE statement in Table Transformer based on 'Status' from JIRA Filter Macro

Question

Hi All,

I'm trying to create a table in confluence that estimates (wildly :) ) the amount of effort  left in a sprint.

I've used the JIRA filter maco to pull in the JIRAs in the current sprint, their status and their story points.

I've wrapped the filter macro in Table Transformer, but I'm having problems with a case statement based on the JIRA status field (which is being displayed as a picture/icon not text.)

Is there a way? I found a similar question about the Issue Type / T field, but that doesn't resolve my issue...

My query...

SELECT
T1.'Status',
COUNT(T1.'Status') AS 'Issues',
SUM(T1.'Story Points') AS 'Story Points',
CASE
WHEN T1.'Status' = 'Closed' THEN 0

--more cases when I get this one to work...
ELSE 1 END AS Factor

--new column to add for Factor * SUM(T1.Story Points)
FROM T*
GROUP BY T1.'Status'

My 'Factor' always comes out as 1.

...I've tried lots of variations on the 'WHEN', using CONVERT or CAST, LIKE and % instead of =... but I'm out of ideas :)

Has anyone overcome this, or is it just not possible...

Niall.

Stiltsoft support · Answer

Seems the initial structure is not correct:

COUNT, SUM and GROUP BY 'Status' count smth and group it by statuses (so you get as many rows in the result table as you have different statuses).

But the CASE WHEN part simply takes your table row by row and places specific numbers considering the status.

So, everything is mixed-up.

Maybe you need smth like this:

SELECT
'Status',
COUNT('Status') AS 'Issues',
SUM('Story points') AS 'Story points',
SUM('Factor') AS 'Factor'
FROM(
SELECT *,
CASE WHEN 'Status' IS "Closed"
THEN 0
ELSE 1
END
AS 'Factor'
FROM T1)
GROUP BY 'Status'

Here we have the internal SELECT where we show all the columns and introduce the new 'Factor' column based on the CASE WHEN condition. Here we can refer to our table as T1.

And the external SELECT takes this new table with the additional 'Factor' column and do the aggregation. Here you can't write T1.'Column name', only the 'Column name' will do - now we work not with the original T1 table.

Here SUM('Factor') AS 'Factor' serves just for an example that firstly you need to set your values based on the condition row by row and only then group these values in the external SELECT.

You may clarify what you need to to do with the 'Factor' column and I'll try to help.

Niall Poole_aj4t7x · Answer

Thanks,

I've done a couple of experiments with FORMATWIKI, so hoping I can work that out later...

It doesn't like Factor in double quotes (displays a syntax error) and in single quotes I get the same result - i.e. always 1 (with or without quotes around the 0 and 1).

Kind regards,

Niall.

Stiltsoft support · Answer

The 'Factor' is not in the quotes - it is a column name as well.

Besides you may leave the THEN 0 and ELSE 1 without the quotes as they are numbers and not text.

What concerns the formatting, the Table Transformer applies its own while calculating data, sometimes it can be inconsistent if the source table has another type of text alignment. You may use the FORMATWIKI function (even not in this Table Transformer macro but in a separate one on top of this) and set the text alignment that you prefer.

Please check this part of our documentation: https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html#Advancedtablecellsformatting-Changingalignment