How to merge tables and delete duplicates from table cells?

Charfi Ouacef May 5, 2023

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_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
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.

Charfi Ouacef May 12, 2023

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

Like # people like this
Charfi Ouacef May 12, 2023

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

Charfi Ouacef May 8, 2023

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 8, 2023

hey @Charfi Ouacef ,

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

Fabio

Charfi Ouacef May 8, 2023

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