Display and filter 2 tables of which 1 sums a column of another

[Fabrice Smith]

Hi everyone,

I am using Table Transformer v11.3.1

I try hard to dynamically refresh a calculation table T1 from a filtered table T2.

Sounds a bit tricky but let me explain...

To simplify the description of the use case, I used simple tables which are 2 distinct CSV files in real life.

Steps:

• Add a Table transformer for T1 to select Transpose result table in settings
• Add an Table Excerpt for T2 named tableT2
• Add a second Table transformer that surrounds the whole. Create a dynamic column called 'Used' to renders the sums of A, B and C from T2.

SQL: note that T1 is transposed in the first Table transformer!

SELECT T1.'Totals', T1.'Init.' AS 'Initial',

CASE

WHEN T1.'Totals' = "A" THEN (SELECT SUM(T2.'A') FROM T2)

WHEN T1.'Totals' = "B" THEN (SELECT SUM(T2.'B') FROM T2)

WHEN T1.'Totals' = "C" THEN (SELECT SUM(T2.'C') FROM T2)

ELSE NULL

END AS 'Used'

FROM T1

• Add a Table Excerpt Include that points to tableT2
• Finally add a Table filter to display both the tables and create filters for Product and Category

Structure:

Now, the issue!
If I want to display the Table Excerpt Include content, I select 'Show a copy...'

And when I filter on Category for example, only T2 is filtered, the calculation is not processed in T1 :(

If I untick 'Show a copy...', then 1. I miss the view of that T2 table, 2. the calculation in T1 is processed!!! but it's impossible to remove the filtered value unless you reset the filters. 

I must be doing something wrong, but I just can't figure it out.

Really struggling to get this working.

I hope someone has a solution :)

[Stiltsoft support · Accepted solution]

Hi @Fabrice Smith ,

Seems that we are talking about the Table Filter, Charts & Spreadsheets for Confluence app.

It will be simpler if you refer to our support where you'll be able to attach a page storage format of your page (upper right corner -> menu ... -> View storage format). We'll be able to reproduce exactly your page and give you some hints.

As for now, it seems that smth is wrong with the macro structure and the Table Filter macro placement.

Why do you need to reuse your T2 firsthand? Just to show the original table? Then your Table Excerpt Include macro should be outside of the Table Filter macro.

And it seems that the Table Filter macro should be placed on top of the Table Excerpt macro. I mean, your original T2 is reused by the Table Excerpt macro and then it is filtered by 'Product' and 'Category'. Then this filtered table is used in combination with the transposed T1.

[Stiltsoft support]

Оr maybe, the idea was to filter the original T2 and get a new combined table every time?

Then the structure will be as following:

• Your original T2 is wrapped in the Table Filter macro and then in the Table Excerpt macro on top (we reuse already filtered table)
• Then for the combining of the two tables inside the Table Transformer macro you put the Table Excerpt Include macro (this macro will get the filtered T2 table) 

[Fabrice Smith]

Hi @Stiltsoft support

Exactly!

The idea is effectively to filter the T2 and dynamically run few calculations in T1.

The solution about the structure you gave me works perfectly. You are amazing!

Big THANKS for your prompt answer and solution! :)