Hi all, I have two PageList Tables with Labels and I want to merge them: * One table pages as Columns, the other as rows * And mark the fields with an "X"where the labels from one table are the page names of the other and vice versa * Mark it with an A, where there is a reference in TableA but missing in TableB * Mark it with an B, where there is a reference in TableB, but missing in TableA As it is hard to explain, I created a picture: Is there a way with TableTransformer or Pivot or? to do this easy? Thanks and best regards, Deborah

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Merge two tables and mark the matching fields

Deborah Frommer

Hi all,

I have two PageList Tables with Labels and I want to merge them:

One table pages as Columns, the other as rows
And mark the fields with an "X"where the labels from one table are the page names of the other and vice versa
Mark it with an A, where there is a reference in TableA but missing in TableB
Mark it with an B, where there is a reference in TableB, but missing in TableA

As it is hard to explain, I created a picture:

Is there a way with TableTransformer or Pivot or? to do this easy?

Thanks and best regards,

Deborah

Accepted answers

Stiltsoft support

Hi @Deborah Frommer ,

As you've mentioned our Table Transformer and Pivot Table macros, we suppose that you use the Table Filter, Charts & Spreadsheets for Confluence app. So, you may refer to our support directly: here we find the related questions manually and some of them can be missed.

We can suggest two options for the case: the first one seems to be simple but the second one 100% fulfills the description.

Option 1

Screenshot 2025-01-16 at 18.02.01.png

You use the Table Transformer macros to flatten your arrays.

For T1:

SEARCH / AS @a EX('Labels'::string->split(" ")) / RETURN(@a->'Page' AS 'Page', _ AS 'Labels', "A" AS 'Table') FROM T1

Screenshot 2025-01-16 at 18.02.29.png

The ::string part is required not to loose empty cells.

For T2 the query is a little bit different:

SEARCH / AS @a EX('Labels'::string->split(" ")) /
RETURN(@a->'Page' AS 'Labels', _ AS 'Page', "B" AS 'Table') FROM T1

Here we crisscrossing Page and Labels columns.

Then we group our tables via the Pivot table macro:

Screenshot 2025-01-16 at 18.03.09.png

And filter out the unwanted empty row and hide the empty column if necessary:

Screenshot 2025-01-16 at 18.03.30.png

Screenshot 2025-01-16 at 18.03.36.png

Here A, B means that there is a cross-reference between two tables and A or B show that the reference is present only in the table A or table B.

All comments

Manoj Kumar Gangwar

Hi @Deborah Frommer

Yes, you can use the Table Transformer macro from the Atlassian Marketplace to achieve this efficiently without manual comparisons.

Steps Using Table Transformer

Insert the Source Tables:
- Add both of your PageList tables to the page.
- Ensure they have consistent column headers for processing (e.g., Table A with pages as columns and Table B with pages as rows).
Wrap Both Tables in the Table Transformer Macro:
- Select both tables.
- Insert the Table Transformer macro around them.
Configure the Table Transformer Macro:
- Use a SQL-like query to merge and cross-reference the data.

Example Query for Table Transformer: Assuming:

Table A: TableA (with pages as columns).
Table B: TableB (with pages as rows).

Use the following query to merge and cross-reference:

SELECT 

COALESCE(TableB."Page Name", 'Not in Table B') AS "Row Headers",

COALESCE(TableA."Page Name", 'Not in Table A') AS "Column Headers",

CASE

WHEN TableA."Page Name" = TableB."Page Name" THEN 'X'

WHEN TableA."Page Name" IS NOT NULL AND TableB."Page Name" IS NULL THEN 'A'

WHEN TableB."Page Name" IS NOT NULL AND TableA."Page Name" IS NULL THEN 'B'

ELSE ''

END AS "Match"

FROM TableA

FULL JOIN TableB

ON TableA."Page Name" = TableB."Page Name"

Visualize the Result:
- The query will output a matrix-like table with the specified conditions:
  - X where there's a match.
  - A where a reference exists in Table A but not Table B.
  - B where a reference exists in Table B but not Table A.
    
    Publish the Page:
    - Once the result is satisfactory, publish the page.

Deborah Frommer

Hi @Manoj Kumar Gangwar ,

thanks for the quick answer.

I am trying it, but have some issues with it.

1. I got an error message in the table transformer because "FULL OUTER JOIN TableB" is needed. (easy to fix)

2. Doesn't matter how I used the input table I got only 3 columns:

one with Title "Row Headers"
one with Title "Column Headers"
one with Title "Match"

What is wrong?

I am also wondering that you have only "Table.'Page Name'" in the query and nowhere the Label

Stiltsoft support

Hi @Manoj Kumar Gangwar ,

Seems that your answer is an AI-generated one - please don't post such answers without checking.

The Table Transformer macro is based on the AlaSQL library with tiny bits of JS, so, besides general recommendations, it is necessary to maintain specific syntax.

For example, the

TableA."Page Name"

parts are not correct: we use " " for strings and ' ' for column names. So, the correct syntax will be T1.'Page' or T1.'Labels'.

Stiltsoft support

Hi @Deborah Frommer ,

We can suggest two options for the case: the first one seems to be simple but the second one 100% fulfills the description.

Option 1

Screenshot 2025-01-16 at 18.02.01.png

You use the Table Transformer macros to flatten your arrays.

For T1:

SEARCH / AS @a EX('Labels'::string->split(" ")) / RETURN(@a->'Page' AS 'Page', _ AS 'Labels', "A" AS 'Table') FROM T1

Screenshot 2025-01-16 at 18.02.29.png

The ::string part is required not to loose empty cells.

For T2 the query is a little bit different:

SEARCH / AS @a EX('Labels'::string->split(" ")) /
RETURN(@a->'Page' AS 'Labels', _ AS 'Page', "B" AS 'Table') FROM T1

Here we crisscrossing Page and Labels columns.

Then we group our tables via the Pivot table macro:

Screenshot 2025-01-16 at 18.03.09.png

And filter out the unwanted empty row and hide the empty column if necessary:

Screenshot 2025-01-16 at 18.03.30.png

Screenshot 2025-01-16 at 18.03.36.png

Here A, B means that there is a cross-reference between two tables and A or B show that the reference is present only in the table A or table B.

Stiltsoft support

Option 2

Screenshot 2025-01-16 at 16.59.00.png

Use the following query inside the Table Transformer macro:

SELECT T1.'Page', T2.'Page' AS 'Page B',
CASE
WHEN TEXT(T2.'Page') IN T1.'Labels' AND TEXT(T1.'Page') IN T2.'Labels' THEN "X"
WHEN TEXT(T2.'Page') IN T1.'Labels' THEN "A"
WHEN TEXT(T1.'Page') IN T2.'Labels' THEN "B"
END AS 'Cross'
FROM
(SELECT *, SPLIT(T1.'Labels'::string, " ") as 'Labels' FROM T1) as T1
OUTER JOIN
(SELECT *, SPLIT(T2.'Labels'::string, " ") as 'Labels' FROM T2) as T2
ON
TEXT(T2.'Page') IN T1.'Labels' OR TEXT(T1.'Page') IN T2.'Labels'

Then group the result table via the Pivot Table macro:

Screenshot 2025-01-16 at 17.03.12.png

And filter out the empty line as we did before:

Screenshot 2025-01-16 at 16.59.49.png

Screenshot 2025-01-16 at 16.59.58.png

Stiltsoft support

Hope that the provided options will help with your case.

But please can you elaborate a little bit on it? How do you you get your original tables? Are they manually created or macro generated? What pages and labels do you store there? Why do you need a cross-reference?

Please describe the case a little bit more - it seems rather interesting and unusual, maybe it will be a good use case for our blog post or smth (maybe other users need the same transformation as well).

Deborah Frommer

Thanks for the help.
I will evaluate it further.

Background:
We have two types of specifications to create our system Architecture: "Concepts" and "BuildingBlocks".
Each spec is an own Page in Confluence, and they are somehow "linked" to each other with a N:N association. (We have a section with Concept Pages and a section with BuildingBlock Pages)
To get an overview of this connection the table should help.

To create the TableA and TableB I am using TableExcerptInclude Macros where I can easily report the labels added to the page and using it further with the TableTransformer.
That was my idea to link them in an easy and flexible way.

Next
I will try what happens if the PageName and the label are different and I have a keyword in the tableExcerpt for mapping.

And thanks for the tip with the Stiltsoft Support Link.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

filter

label

data-center

table

table-filter

merge

table-filter-and-charts

confluence-data-center

table-transformer

table-filter-charts-spreadsheets

pivot-table