replace 0 with empty value in table

[emagnun]

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. 

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. 



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

[Marc - Devoteam · Accepted solution]

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]

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]

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]

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'

[emagnun]

@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]

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.