Applying a formula in the column rules within the table tool

[maxproie]

Hey Alteryx Community, hoping that you can help me out with a very interesting problem I'm running into.   Through a long workflow I've created a matrix with the hope to conditionally format the background color of values within a table tool based on the string that is also within the same cell.  I've successfully done that coloring but before the table is rendered, I want to take out the non alphanumeric so that the result is just a number with a new background color.   The coloring is created by conditional column rules within the table tool.  You can see my current output in the first screenshot below.

Like I mentioned above, I want to now take out the color identifier which I'm hoping is possible. I have also attached the workflow so you can see what I'm doing. There is an additional formula tool added to show the formula I'm trying to use is successful in pulling out the non alphanumeric (Yes, no ect) so I don't think its the way that the formula is written. 

Been struggling with this for a while so really hoping the community can help me out! 

[Jonathan-Sherman · Accepted solution]

Hi @maxproie,

I believe this is what you're looking to achieve?

I've duplicated the column to create [Group 1 Duplicate] and [Group 2 Duplicate]. This allows me to change the values for [Group 1] and [Group 2] and reference [Group 1 Duplicate] and [Group 2 Duplicate] values in the formulas for colour conditioning of [Group 1] and [Group 2] fields. Then removing the [Group 1 Duplicate] and [Group 2 Duplicate] fields.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[AbhilashR]

@maxproie - how about add colors based on row rules, and explicitly make column 1 and column 2 with no colors? Attached is an attempt to come up with something you are looking for. Let us know if this works.

EDIT -  I think @Jonathan-Sherman's solution is better suited for what you are trying to achieve.

[CharlieS]

I also like @Jonathan-Sherman's solution, using supplemental fields is definitely the way to go. Here's another way to use additional fields to define colors just to have another option. 

I establish the color values for each field ahead of the formula tool and only have one formatting rule in the table tool. I find this easier to troubleshoot color issues instead of opening the Table rules each time. The Multi-Field Formula tool is not necessary, but makes the translation from color description to RGB values easier to digest. 

Once I have the RGB values established for each field, I use the following formula in the Table tool:

"background-color:rgb(240,240,240);"

Which I update for each field to look like this:

"background-color:"+[Group1Color]+";"

Here's the documentation on table Style Rules:

https://help.alteryx.com/current/TableStyleRules.htm 

[maxproie]

Hey Jonathan Really loved this solution.  Although there are 19 groups in my actual matrix, it didn't take too long to build out the formula for each and once I did, it worked perfectly.  Once I was in the column rules, I was able to utilize the trick in this post: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Apply-Column-Rules-to-Multiple-Colu... so that I only had to write the rules once in the table tool as well.  Helped a ton and will make it simpler to add more groups or take out groups in the future. Thanks so much for the help everyone!