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,555,109
Community Members
 
Community Events
184
Community Groups

How to merge tables and delete duplicates from table cells?

Hello together,

 

I would like to merge these two tables from different pages:

Screenshot 2023-05-05 175436.png

to this final table:

Screenshot 2023-05-05 175554.png

My problem currently is that i have no idea how to remove the duplicate values.

Can somebody please help me?

2 answers

1 accepted

5 votes
Answer accepted
Katerina Rudkovskaya _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.
May 11, 2023

Hi @Charfi Ouacef,

Please try the following SQL query for the case:

Thu 1-1.png

SELECT
FORMATWIKI(SUM('male' + "\n")) AS 'male',
FORMATWIKI(SUM(DISTINCT('beard') + "\n")) AS 'beard',
FORMATWIKI(SUM(DISTINCT('shoe size') + "\n")) AS 'shoe size'
FROM (SELECT * FROM T1 OUTER JOIN T2 ON T1.'male' = T2.'male')

Thu 1-2.png

Seems it can help your case.

Thats exactly what I was looking for. Thank you very much!

Like # people like this

Hi @Katerina Rudkovskaya _Stiltsoft_ ,

just one little question. If I have more information in a cell, then the words are simply lined up.

Screenshot 2023-05-12 145256.png

SELECT
FORMATWIKI(SUM('male' + "\n")) AS 'male',
FORMATWIKI(SUM(DISTINCT('beard') + "\n")) AS 'beard',
FORMATWIKI(SUM(DISTINCT('shoe size') + "\n")) AS 'shoe size'
FROM (SELECT * FROM T1 OUTER JOIN T2 ON T1.'male' = T2.'male')

Screenshot 2023-05-12 145359.png

Is there a way to separate the words to merge them like discussed? I also have the possibility to change the original tables but i don't want to create a row for every Information in the cell.

 

Best regards
Ouacef

Stiltsoft support
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.
May 12, 2023

The contents of the specific cell can't be treated separately.

If "John" has

yes(enter)

maybe(enter)

no

in his cell, then all the cells' contents will be compared with the other cells.

So, your result that is

yesmaybeno

is correct.

If such behavior suits you and the question is that the result text is unreadable, you may just use spaces:

yes (enter)

maybe (enter)

no

Then you'll get 

yes maybe no

as a result.

If you need to compare items separately, you indeed need to create several rows for "John". And don't forget to use the DISTINCT function for 'male' column, otherwise you'll get several "Johns".

0 votes
Fabio Racobaldo _Herzum_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 08, 2023

Hi @Charfi Ouacef and welcome,

tables in Confluence are not structured items so you should do that manually. There are not builtin features to do that.

Fabio

Hi @Fabio Racobaldo _Herzum_ ,

thank you very much for the fast answer. Is there no possibility to solve the problem with table transformation and SQL?

Fabio Racobaldo _Herzum_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 08, 2023

hey @Charfi Ouacef ,

tables are stored as text in Confuence DB so you can't manage merge from text using SQL query.

Fabio

Hi @Fabio Racobaldo _Herzum_ ,

In sql it is possible to join the same text in cells using "Group By" command. Is there no possibility to use another operator, something like FORMATWIKI(SUM...) or "Select Distinct"??

@Katerina Kovriga _Stiltsoft_ do you maybe have a solution for the problem?

Best regards
Ouacef

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events