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 ',

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

Sure @Katerina Kovriga {Stiltsoft} ,Thank you.

[Katerina Kovriga {Stiltsoft}]

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

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:

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]

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

[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 source table (Extracted from Excel)

[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")))