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

[Stiltsoft support · Accepted solution]

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

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

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:

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

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

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:

And filter out the empty line as we did before:

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

[Stiltsoft support]

Thanks!

And please don't hesitate to refer to the support portal - all the tickets there are registered and the portal is also confidential (you'll be able to share your data, screenshots, storage formats and we'll easily recreate your macro structure and help you with the solution).

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

1. 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).

2. Wrap Both Tables in the Table Transformer Macro:

   • Select both tables.
   • Insert the Table Transformer macro around them.

3. Configure the Table Transformer Macro:

   • Use a SQL-like query to merge and cross-reference the data.

4. 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"

5. 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 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 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'.