replace 0 with empty value in table

emagnun
Contributor
October 21, 2024

I have a jira macro table output that shows teams data across financial year as a table. If the data is 0, i want an empty cell value to be displayed in my table. 

tt.jpg

I used the above table transformer and it worked fine for the values "0". But i found that it just replaced the character 0 with "", so even a number 50 will be displayed as 5. 

tt.jpg

Pls help with any workaround or better ways. @Stiltsoft support 

2 answers

1 accepted

4 votes
Answer accepted
Marc - Devoteam
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 21, 2024

HI @emagnun 

I think the best is to contact stiltsoft support, as table transformer function is part a their app Table Filter, Charts & Spreadsheets for Confluence.

As looking into the documentation doesn't provide me a quick option or fix.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 21, 2024

Hi @emagnun ,

If your questions are related to our Table Filter, Charts & Spreadsheets for Confluence app and you want to get help from our support, please refer to the support portal as @Marc - Devoteam has kindly suggested. Here we find related questions manually and some of them can be missed. Besides the portal is confidential so you'll be able to share your data freely.

Meanwhile, your SQL query works as expected: it replaces all the occurrences of "0" with "". So, 50 -> 5 is correct.

For your case, you may try to use the CASE WHEN statement.

The query will look smth like this:

SELECT *,
CASE WHEN 'Column 1' = 0
THEN ""
ELSE 'Column 1'
END
AS 'Column 1'
FROM T1

emagnun
Contributor
October 21, 2024

Thank you, I thought of CASE WHEN , but this will be a tedious task as I may have more number of columns in reality.

Is there any other way that you can advise? 

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 21, 2024

The 'Column 1' was just an example, you may copy this part of the query for each column that you need:

CASE WHEN 'Column 1' = 0
THEN ""
ELSE 'Column 1'
END
AS 'Column 1'

0 votes
emagnun
Contributor
October 21, 2024

@Stiltsoft support 

I have an additional question regarding this. I need to remove a row where every column value, aside from the first one, is zero. What SQL query can I use to do this?

For instance, if all team values for FY2122 are zero, I want to eliminate that row from my final table.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 21, 2024

Hi @emagnun ,

Seems that you've already asked this question here: https://community.atlassian.com/t5/Confluence-questions/Removing-columns-with-empty-data-using-table-transformer/qaq-p/2843143

As you talk about rows, you need to visit the Settings tab of the macro and enable both the Transpose source tables and Transpose result table options.

I mean, at first the source table is transposed, its rows become columns. Then your query removes empty columns. Then the result table is transposed and your columns become rows as they were before.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events