nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Formatwiki in table transformer- New row created with zeros

Karthika_Natarajan

I used formatwiki in table transformer query where I do a sum operation to list the values of the column. My result query has a new row with zeros. How to trim this new row? or How to avoid it?

FORMATWIKI(SUM('Test' + "\n")) AS 'Test ',

Formatwiki issue.png

Accepted answers

All comments

Katerina Kovriga _Stiltsoft_

Hi @Karthika_Natarajan ,

Please give us the full screenshot of the source table and the full SQL query you are using to help with the issue.

Karthika_Natarajan

Hi @Katerina Kovriga _Stiltsoft_

Please find the query below

SELECT
FORMATWIKI("*P1*"+"\n"+"*P2*"+"\n"+"*P3*"+"\n"+"*Total*") AS 'Priority',
FORMATWIKI(SUM('Total' + "\n")) AS 'Total Cases',
FORMATWIKI(SUM('Automatable' + "\n")) AS 'To Do',
FORMATWIKI(SUM('Automated' + "\n")) AS 'Done'
FROM
(SELECT
CASE
WHEN 'RN' IN (1,5,9)
THEN "P1"
WHEN 'RN' IN (2,6,10)
THEN "P2"
WHEN 'RN' IN (3,7,11)
THEN "P3"
WHEN 'RN' IN (4,8,12)
THEN 'Total cases'
END AS 'Priority',
CASE
WHEN 'RN' IN (1,2,3,4)
THEN 'Total' END AS 'Total',
CASE
WHEN 'RN' IN (5,6,7,8)
THEN 'Total' END AS 'To Do',
CASE
WHEN 'RN' IN (9,10,11,12)
THEN 'Total' END AS 'Done'
FROM
(SELECT ROWNUM() AS 'RN',
T1.'__' AS 'Total' FROM T1 WHERE T1.'__' NOT IN ("Total")))

Karthika_Natarajan

This is the source table (Extracted from Excel)

Source table.png

Katerina Kovriga _Stiltsoft_

And may I see the source table? I see from the query that you use plenty of columns where you try to sum up values and on the screenshot I see a noname column with numbers, text and empty cells. It looks like a result column or smth.

Karthika_Natarajan

This is the excel from where I Extract the source table by transposing it.

Katerina Kovriga _Stiltsoft_

I've tried to reproduce the issue (maybe a little bit simplified, but the structure looks similar to yours):

Wed 5-1.png

SELECT
FORMATWIKI(SUM('Total' + "\n")) AS 'Total New'
FROM
(SELECT
CASE
WHEN 'RN' IN (1,5,9)
THEN "P1"
WHEN 'RN' IN (2,6,10)
THEN "P2"
WHEN 'RN' IN (3,7,11)
THEN "P3"
WHEN 'RN' IN (4,8,12)
THEN 'Total cases'
END AS 'Priority',
CASE
WHEN 'RN' IN (1,2,3,4)
THEN 'Total' END AS 'Total',
CASE
WHEN 'RN' IN (5,6,7,8)
THEN 'Total' END AS 'To Do',
CASE
WHEN 'RN' IN (9,10,11,12)
THEN 'Total' END AS 'Done'
FROM
(SELECT ROWNUM() AS 'RN', 'Total' FROM T*))

Here is my result:

Wed 5-2.png

The FORMATWIKI(SUM('Total' + "\n")) works correctly and the nested SQL queries seem to be fine.

Maybe it will be better to raise a support ticket where you could attach the original Excel file and the Page Storage Format (upper right corner -> menu ... -> View Storage Format). We'll be able to look into your exact page.

Karthika_Natarajan

Sure @Katerina Kovriga _Stiltsoft_ ,Thank you.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server