Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,466,394
Community Members
 
Community Events
176
Community Groups

Formatwiki in table transformer- New row created with zeros

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

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

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

This is the source table (Extracted from Excel)

Source table.png

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.

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

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

Comment

Log in or Sign up to comment
TAGS

Atlassian Community Events