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

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.