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 · Accepted solution]

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. 

[Ammar Albarghouthi]

Hello. Thank you for your help. I tried it and it works.

I was just hoping I could accomplish the same without have to create that additional table. In your steps described above I'd have to maintain the values in both tables, as opposed to capturing the column name from my source like intended.

At least in the interim it's functional :)

[Stiltsoft support]

Hi @Ammar Albarghouthi ,

The Table Transformer macro is based on the AlaSQL library, it refers to the source table as a mini database where headers are column names. Like you call them and extract data from them. Of course, it is possible to transpose tables but I don't see any use here for our case.

Maybe it is better to use the Table Spreadsheet macro? For all the tables, I mean. You'll be able to create a fully functional Excel spreadsheet with cell formulas, conditional formatting, cells' reference on the same sheet and other sheets, etc. 

You may use the existing Table Filter macro to export your first three tables in csv format and then import this file to the Table Spreadsheet macro. Then you add live formulas to the "Average row" and create the second summary table referring to the cells from the first three source tables.

[Ammar Albarghouthi]

Thank you for the explanation, it makes sense now.