Table transformer_How can I subtract filtered data

[Alice Liu]

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?

[Stiltsoft support · Accepted solution]

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:

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.

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:

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:

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.

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.

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:

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.

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.

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]

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!

[Stiltsoft support]

Hi @Alice Liu ,

The message you see is an error belonging to the external Table Transformer macro when it tries to process the unfiltered data. If you clear the filtering panels or choose more than one ID for each field and go to the preview of the internal Table Transformer macros, you'll see that you indeed get "wrong" tables - all the columns will be named 'Row 1' and 'Row 2' respectively. As the Table Transformer macro treats the source tables as mini databases, it is confusing for the macro, hence the error.

As usual, we have an option to remove such system messages (for example, if the query returns no entries or smth): you may use another Table Transformer macro on top with the default SQL query "SELECT * FROM T*", then go to the Options tab -> Source data and choose to Hide all content.

But this option won't work for your case: not only the error message will be hidden, but the filtering panels as well (and you need to filter your tables on the fly).

Maybe it will be more clear for your users to use an Info/Warning macro before the Table Toolbox macro indicating the correct behavior: to choose one item per filtering panel.

[Alice Liu]

Understood and thanks for the detailed explanation. I will use warning message to avoid user's confusion.

[Stiltsoft support]

Hi @Alice Liu ,

I've also consulted with our developers regarding your case: they came up with a kind of a workaround where you were not supposed to get any errors.

Wrap your table (one copy, not two) inside the Table Filter macro and then inside the Table Transformer macro.

Set the dropdown filter for the 'ID' column as we have done before.

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

SELECT * FROM T1 LIMIT 3
UNION ALL CORRESPONDING
SELECT "Difference" as 'ID',
SUM(IF('N' = 1, 'Col 1', 'Col 1' * -1)) AS 'Col 1',
SUM(IF('N' = 1, 'Col 2', 'Col 2' * -1)) AS 'Col 2',
SUM(IF('N' = 1, 'Col 3', 'Col 3' * -1)) AS 'Col 3',
SUM(IF('N' = 1, 'Col 4', 'Col 4' * -1)) AS 'Col 4',
SUM(IF('N' = 1, 'Col 5', 'Col 5' * -1)) AS 'Col 5'
FROM
(SELECT *, ROWNUM() as 'N' FROM T1)

The "Transpose source tables" and "Transpose result table" options should be unchecked, we don't need them here.

Now when your users select one ID, they will get this specific row and the Difference row that will match this ID row.

If your users select two IDs, they will get two rows of data and the Difference row. The only peculiarity is that there is no any difference in the order: 10 and 20 will be -10 as well as 20 and 10 will be -10. 

If your users select 3 and more IDs, then they will be shown the first three rows of your table without any calculations.

You may check the behavior yourself and see what suits you more. Seems that this is all we can do here.)