9.5.1
I have a table, where I have column 1 as Sub categories for tickets, with the department that should handle it in ()'s. Row1 columns 2+ are the names of the departments, and the cross section are the count of which department completed the ticket.
Is there a way to format the cells when the department that has a count, is not the named department in column 1? Would this require regex?
Please post at least a bogus table with fake data and not blurred
| Category | dept1 | dept2 | dept3 |
|---|---|---|---|
| Account/Creation (dept1) | 6 | 0 | 0 |
| Account/Deletion (dept1) | 1 | 3 | 0 |
In this example, I would need the square of 3 in row 3 to be highlighted, because dept 2 worked a ticket that should have stayed with dept1.
I do not have the ability to create dummy data in our databases, the best I could do would be taking a screenshot of excel of the above table.
Understood, but in order to help you we have to have dummy data like you provided. 
it helps us help you
Do your categories actually have the department embedded in them?
Account/Creation (dept1)
Yes, that is correct.
All of our sub categories have the department name in ()'s
first/second (department)
Account/Create (dept2)
Login/Password (dept1) ect ect
It is like that in the database itself?
The database pulls it from our ticketing system, so yes. It is translated, there is a table of category id’s and names associated with the id’s, but it is in that format.
The plot gets thicker. This is a bad design that will require hacking to get what you want. This will require you to provide us details of the table(s) schema with data types etc. Cant work with this. How will you connect it back to dept when it is in parenthesis. Unless I am missing something
select
(select Name FROM Subcategories WHERE SubCategoryId = Tickets.SubCategoryId) AS SubCategory
, SUM(CASE WHEN WorkedBy = ‘Dept1’ Then 1 ELSE 0 END) AS Dept1
, SUM(CASE WHEN WorkedBy = ‘Dept2’ Then 1 ELSE 0 END) AS Dept2
, SUM(CASE WHEN WorkedBy = ‘Dept3’ Then 1 ELSE 0 END) AS Dept3
, SUM(CASE WHEN WorkedBy = ‘Dept4’ Then 1 ELSE 0 END) AS Dept4
, SUM(CASE WHEN WorkedBy = ‘User’ Then 1 ELSE 0 END) AS User
From table
Where $__timeFilter(created)
This is what I’ve got, redacted of course.
I know I can do a regex on Row 1 (SubCategory, Dept1, Dept2, Dept3, Dept4, User). But I am unsure of how to preform a regex on the value in column 1 (subcategory names) and compare that against if there is data in a certian column.
you need to seriously reconsider this design pattern of category name (departmentname) thing.
It is going to make what you want to do impossible and a hack at best. Are you open to a redesign? Otherwise sorry but I cannot help you myself with this design approach. I am getting rashes and hives 