How to create color rules for all columns with the same prefix

[SC Chou]

Hi,

I know how to set colorrules for each column in a table. However, the data keeps updating and Imight need to keep track of the data and create new rules when there arenew columns.

To have zero maintenance reuqired on myreport, I'm seeking a way to set color rules for all columns with prefix 'DF' using IronPython:

When the date in column DFxx1 is the same as the datein the 1st row, set (1,2)to yellow, otherwiseset (1,2) it to red...iterate this this process for all items in this table.

 

 

 

 

1

2

3

 

 

1

last_modified(timestamp)

DFxx1

DFyy2

 

 

2

mm/dd/yyyy

mm/dd/yyyy

mm/dd/yyyy

 

 

3

mm/dd/yyyy

mm/dd/yyyy

mm/dd/yyyy

 

 

 

 

I think I first need to use regular expression so I tried the solution in this page:

https://community.spotfire.com/wiki/how-apply-regular-expression-search-criteria-filter-tibco-spotfire-using-ironpython-scripting

However, I got this this error message:

"Exception: Could not find column 'myTable.cname'."

 

Now I am stucked, and don't know how to move on...could anyone help me

[Sayali Patil]

Hello ,

The article you shared is meant to search for specific values based on the search criteria in a column filter ,in the example a list box filter.

cname is not a keyword but it needs to be replaced with the name of the column (I have updated the article with that note now).

For your case you cant select the required columns using this script

allcols = Document.Data.Tables["SalesAndMarketing"].Columns.FindAll("Brand*")

for c in allcols:

print c

Here the search criteria is all column names with the word Brand in their Names.

Now you can write the required rules on these columns for the required visualizations. Here is a reference script -

 

from Spotfire.Dxp.Application.Visuals import TablePlot

from System.Drawing import Color

from Spotfire.Dxp.Application.Visuals.ConditionalColoring import Coloring

from Spotfire.Dxp.Application.Visuals import CategoryKey

 

tablePlot = vis.As[TablePlot]()

coloring = tablePlot.Colorings.AddNew("Test1")

coloring.DefaultColor = Color.Green

categoryKey = CategoryKey("Rank");

coloring.AddExpressionRule("[Rank] > 4900", Color.Yellow);

coloring.AddExpressionRule("[Rank] < 4800", Color.Tomato);

coloring.AddExpressionRule("[Rank] =4865 ", Color.PeachPuff);

tablePlot.Colorings.AddMapping(categoryKey, coloring);

 

You will need to add individual expression for each of the columns you need to color. Same rule cannot be shared between multiple columns

 

Thanks!