How to capture the name of columns into rows?

Ammar Albarghouthi

Hi. I'm trying to build a Vendor Scorecard. For that, I have two Table Toolboxes

The first Table Toolbox houses a Table Excerpt macro with three tables (my source tables) each pertaining to a topic: Requirements, Demo, and Price (the names of the first column of my three tables respectively). The table excerpt is wrapped in a Table Filter that calculates the average score of each column

 

The second Table Toolbox has a Table Excerpt Include, which references the Table Excerpt above and is wrapped in a Table Transformer with SQL Query. The purpose behind the Table Transformer is to output a Table with the Criteria Scores (Requirements, Demo, Price), The weight and weighted score (weight times avg score)

 

My issue is that I don't know how to dynamically capture the names of the first column of my source tables (i.e. Requirements, Demos, Price) and insert them in the SQL query so that the output would list "Requirements" in the first row, "Demos" in the second row, and "Price" in the third row

I hope I made my self clear. Can anyone help?

Stiltsoft support

Hi @Ammar Albarghouthi,

The workaround may be as following: recreate the structure given below inside your second Table Toolbox macro.

Your Table Excerpt Include stays in the internal Table Transformer macro and you add another external Table Transformer macro that wraps the internal Table Transformer and an additional manually created table with autonumbering and criteria scores names.

Here is the SQL query for the internal Table Transformer macro:

SELECT ROWNUM() AS 'Table Number',
ROUND(FORMATNUMBER(.25), 2) AS 'Weight',
ROUND(FORMATWIKI(AVG('Outreach')*0.25), 2) AS 'New Outreach',
ROUND(FORMATWIKI(AVG('Revenue')*0.25), 2) AS 'New Revenue'
FROM T*

Here we leave blank the field with decimal places in the Table Transformer macro settings and use the ROUND() function if required. Thus we are able to use a simple ROWNUM() function without any unwanted decimal places.

And here is the SQL query for the external Table Transformer macro:

SELECT
T2.'Name' AS 'Criteria scores',
T1.'Weight', T1.'New Outreach',
T1.'New Revenue' AS 'New Revenue'
FROM T1 LEFT JOIN T2 ON T1.'Table Number' = T2.'__'

Here we merge our two tables by the table numbers and get the result table that is easier to understand when the page is published.

Hope it helps.