Formatwiki in table transformer- New row created with zeros

Karthika_Natarajan March 15, 2022

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

1 comment

Katerina Kovriga {Stiltsoft}
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.
March 15, 2022

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.

Like # people like this
Karthika_Natarajan March 16, 2022

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 March 16, 2022

This is the source table (Extracted from Excel)

Source table.png

Katerina Kovriga {Stiltsoft}
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.
March 16, 2022

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 March 16, 2022

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

Katerina Kovriga {Stiltsoft}
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.
March 16, 2022

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.

Like Karthika_Natarajan likes this
Karthika_Natarajan March 17, 2022

Sure @Katerina Kovriga {Stiltsoft} ,Thank you.

Comment

Log in or Sign up to comment
TAGS
AUG Leaders

Atlassian Community Events