Hi there, I tried to search the resources but got stuck and hope someone can help. My question is how to calculate the subtraction only using the filtered data? The source data is like below: [ID Filter: (Dropdown menu)] ID12345Type 1102051005000Type 2335811550Type 32284454477 My goal is to let users freely choose 2 IDs, and then the table can automatically calculate the difference of row value. For example, users choose ID 2 and ID 3, and the table automatically calculate Type 1/2/3 of ID 3 - Type 1/2/3 of ID 2. Wonder if you can provide any recommendations for achieving this goal?

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; } } }

Table transformer_How can I subtract filtered data

Alice Liu_gjljc8

Hi there,

I tried to search the resources but got stuck and hope someone can help.

My question is how to calculate the subtraction only using the filtered data?

The source data is like below:

[ID Filter: (Dropdown menu)]

ID	1	2	3	4	5
Type 1	10	20	5	100	5000
Type 2	3	35	81	15	50
Type 3	22	84	45	44	77

My goal is to let users freely choose 2 IDs, and then the table can automatically calculate the difference of row value.

For example, users choose ID 2 and ID 3, and the table automatically calculate Type 1/2/3 of ID 3 - Type 1/2/3 of ID 2.

Wonder if you can provide any recommendations for achieving this goal?

Accepted answers

Stiltsoft support

Hi @Alice Liu ,

Seems that there is smth wrong with the example of your table: if every ID has three types (and your last sentence points at that), then the existing 'ID' column should be named 'ID Type' or smth and there should be another separate column for the 'ID' itself.

Please see the screenshot below:

Fri 7-1.png Here when you filter by the 'ID', you'll get three rows (Type 1, Type 2, Type 3 for each ID). Let us call this variant as option 1.

And here is the option 2: with a table from your screenshot.

Fri 7-2.png

Here when you filter by the 'ID', you'll get one row every time: each ID is just some type with a number.

So, let me describe these two cases separately, though the first steps are the same.

At first, place the two copies of your tables inside the Table Toolbox macro. Here I manually copied my table twice but this was done for simplicity. In real life it is better to reuse your table with the help of the Table Excerpt/Table Excerpt Include macros. So, if you need to update your table, you will work with the source table and the updates will be pushed to the reused copy automatically.

Then go to the Table Toolbox macro and create the following macro sequence inside its shell:

Fri 7-3.png

Each table is wrapped in the Table Filter and then in the Table Transformer macro (let's call them internal). And then everything is wrapped in another Table Transformer macro (external).

So, these steps are the same for the two options.

All comments

Stiltsoft support

Hi @Alice Liu ,

Please see the screenshot below:

Fri 7-1.png Here when you filter by the 'ID', you'll get three rows (Type 1, Type 2, Type 3 for each ID). Let us call this variant as option 1.

And here is the option 2: with a table from your screenshot.

Fri 7-2.png

Here when you filter by the 'ID', you'll get one row every time: each ID is just some type with a number.

So, let me describe these two cases separately, though the first steps are the same.

Then go to the Table Toolbox macro and create the following macro sequence inside its shell:

Fri 7-3.png

Each table is wrapped in the Table Filter and then in the Table Transformer macro (let's call them internal). And then everything is wrapped in another Table Transformer macro (external).

So, these steps are the same for the two options.

Stiltsoft support

Option 1

Go to the Table Filter macro and set the dropdown filter for the 'ID' column and then choose to hide the 'ID' column:

Fri 7-4.png

Do it for both Table Filter macros.

Then go to the first internal Table Transformer macro and use the following SQL query:

SELECT 'ID Type',
'Type 1' AS 'First ID 1',
'Type 2' AS 'First ID 2',
'Type 3' AS 'First ID 3'
FROM T*

Go to the Settings tab and check the "Transpose source tables" option.

Fri 7-5.png

Then go to the second internal Table Transformer macro and use the following SQL query:

SELECT 'ID Type',
'Type 1' AS 'Second ID 1',
'Type 2' AS 'Second ID 2',
'Type 3' AS 'Second ID 3'
FROM T*

Go to the Settings tab and check the "Transpose source tables" option.

Then go to the external Table Transformer macro and use the following SQL query:

SELECT T1.'ID Type',
'First ID 1' - 'Second ID 1' AS 'Difference 1',
'First ID 2' - 'Second ID 2' AS 'Difference 2',
'First ID 3' - 'Second ID 3' AS 'Difference 3'
FROM T1 OUTER JOIN T2 ON T1.'ID Type' = T2.'ID Type'

Go to the Settings tab and check the "Transpose result table" option.

Fri 7-10.png

Here is your result: you select one ID in each filter panel and get the three rows of subtraction.

As our sets of rows were identical, we got all nulls in the result table (the calculations were performed correctly).

Stiltsoft support

Option 2

Go to the Table Filter macro and set the dropdown filter for the 'ID' column:

Fri 7-7.png

Do it for both Table Filter macros.

Then go to the first internal Table Transformer macro and use the following SQL query:

SELECT
"Row 1" AS 'Row',
'Col 1', 'Col 2', 'Col 3', 'Col 4', 'Col 5'
FROM T*

Go to the Settings tab and check the "Transpose result table" option.

Fri 7-8.png

Then go to the second internal Table Transformer macro and use the following SQL query:

SELECT
"Row 2" AS 'Row',
'Col 1', 'Col 2', 'Col 3', 'Col 4', 'Col 5'
FROM T*

Go to the Settings tab and check the "Transpose result table" option.

Then go to the external Table Transformer macro and use the following SQL query:

SELECT T1.'Row', 'Row 1', 'Row 2',
'Row 1' - 'Row 2' AS 'Difference'
FROM T1 OUTER JOIN T2 ON T1.'Row' = T2.'Row'

Go to the Settings tab and check the "Transpose result table" option.

Fri 7-9.png

Here is your result. You may choose not to show the 'Row 1' and 'Row 1' data as well:

SELECT T1.'Row',
'Row 1' - 'Row 2' AS 'Difference'
FROM T1 OUTER JOIN T2 ON T1.'Row' = T2.'Row'

Hope this helps your case.

Alice Liu_gjljc8

Hi there,

Thank you very much!!! Option 2 is exactly what I'm looking for )

And Option 1 is also inspiring for potential future case that I had not think of it should be a separated column for ID.

Back to my case with option 2, I have 1 remaining question is that after using above suggestions, it will show an error message of the external table transformer at the beginning: "Cannot resolve column "Row 1" ("ID 1") because it exists in two sources tables" as below.

It actually has no impact to the function because once there are inputs in the filter, this error disappear. But I'm thinking whether can set a rule to replace the error message to reminder, such as "Please select the ID and project..."?

Appreciate again that the suggestions saved my life!

error message.PNG

Welcome!

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

Quick Links

Find more posts tagged with

confluence

sql

cloud

confluence-cloud

filter

table

table-filter

table-filter-and-charts

table-transformer

table-toolbox