Including counts with DISTINCT embedded in FORMATWIKI

Scott Gillespie_ibixpx

This question is about how to combine some functions within Table Transformer SQL to add more information to a display table.

I have the following data table:

Engagement	Engagement Team	Escalation?	Watch List? (Yes or No)	Watch List Category
ABC	ABC	No	No	Resources
XYZ	XYZ 1	No	Yes	FTL Issue
XYZ	XYZ 2	Yes	Yes	Resources

I use Table Transformer to create a summary table showing total number of engagements, engagement teams, number of engagement escalations ("Yes"), number of Watch List engagements ("Yes") and a list of the unique watch list categories, displaying this table:

 

Total Engagement Teams	Total Engagements	# Engagement Teams on Watch List	# Engagement Escalations	Active Watch List Categories
3	2	2	1	FTL Issue Resources

The Table Transformer SQL is below and works fine. It would be helpful to show how many instances there are of each watch list category, such that the display would show in the last column, for example:

1 - FTL Issue

2 - Resources

In the last FORMATWIKI statement, I have tried many different ways to include COUNT to add the quantity to the DISTINCT categories, but it always fails, either not displaying anything or giving an error. I suspect I am simply not using the right syntax because the statement is rather complex, but I have run out of ideas on how to set up.

Please suggest if there is a way to accomplish this within the existing FORMATWIKI statement. I'm open to re-working the SQL if my current code is too inefficient or clumsy.

SELECT
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", COUNT('Engagement Team'), "{cell}")
AS 'Total Engagement Teams',
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", COUNT(DISTINCT 'Engagement'), "{cell}")
AS 'Total Engagements',
FORMATWIKI("{cell:width=250px|align=center|font-size=50px|font-weight=bold}", SUM(CASE WHEN 'Watch List? (Yes or No)' = "Yes" THEN 1 ELSE 0 END), "{cell}")
AS '# Engagement Teams on Watch List',
FORMATWIKI("{cell:width=250px|align=center|textColor=Red|font-size=50px|font-weight=bold}", SUM(CASE WHEN 'Escalation?' = "Yes" THEN 1 ELSE 0 END), "{cell}")
AS '# Engagement Escalations',
/* Display list of unique watch list categories currently in use */
FORMATWIKI("{cell:width=250px|font-size=20px|font-weight=bold}", (SELECT SUM(DISTINCT('Watch List Category') + " \n")
FROM T1 WHERE 'Watch List Category' <> "N/A"), "{cell}")
AS 'Active Watch List Categories'
FROM T1

Stiltsoft support

Hi @Scott Gillespie ,

The issue is that all your queries give you one number (one cell) and in the last cell you want to combine several numbers with an explanation (smth like aggregated table).

The workaround may be to reuse your source table with the help of the Table Excerpt/Table Excerpt Include macros and create a simple pivot table (you may use the Pivot Table macro for this purpose) and count how many of which categories you have.

Or if your categories are static (there are only specific names for them) you may count each one category separately in its own cell with its own header (for example, like you've count the Engagement Escalations).