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

[Niall Poole]

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]

Hi @Niall Poole ,

Seems the issue is much easier: please check your formatting.

Not 

WHEN T1.'Status' = 'Closed' THEN 0

but

WHEN T1.'Status' = "Closed" THEN 0

By 'Column_name' we refer to columns and by "String" we refer to strings (cell contents).

[Niall Poole]

Hi @Stiltsoft support 

Thanks for the quick reply, but it's not worked....

I updated my query...

But the result is the same...

Something going on with the story point formating too, but that's less fundamental...

Kind regards,

Niall.

[Niall Poole]

Don't get me started on having a 12 point JIRA ;)

[Stiltsoft support]

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 

[Niall Poole]

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]

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.