How to count values across multiple columns in Spotfire similar to CountIf function – Revvity Signals

Date Posted: 2019-02-06
Product: TIBCO Spotfire®

Problem:

How to count values across multiple columns in Spotfire similar to CountIf function

Solution:

Suppose you have four columns of Year (Year1, Year2, Year3, Year4) and you want to count the occurrence of Year1 column's value that is present in all four columns. Take the example data:

Region	Year1	Year2	Year3	Year4
MW	2013	2014	2015	2016
NE	2013	2014	2015	2016
SE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2016	2017	2018	2019
WE	2014	2015	2016	2017
WE	2014	2015	2016	2017
WE	2014	2015	2016	2017
WE	2014	2015	2016	2017
WE	2014	2015	2016	2017
WE	2014	2015	2016	2017
WE	2014	2015	2016	2017
WE	2013	2014	2015	2016
WE	2013	2014	2015	2016
NE	2013	2014	2015	2016
SE	2013	2014	2015	2016
NE	2013	2014	2015	2016
SE	2013	2014	2015	2016
NE	2013	2014	2015	2016
SE	2013	2014	2015	2016
NE	2013	2014	2015	2016
SE	2016	2017	2018	2019
NE	2016	2017	2018	2019
SE	2016	2017	2018	2019

In the case of 2016 from Year1 column, there are 14 occurrence in Year1, 7 occurrence in Year3 and 11 occurrence in Year4 column so the desired total would be 14+7+11 which equals 32. In the same way, you want to count the occurrence of every distinct value from Year1 out of all four columns. To achieve this you can use either of the two approaches:

Option 1) Use an unpivot transformation and transform the 4 year columns to one column:

Select Insert > Transformations...
   Added transformations
        Transformation name: Unpivot
        Columns to pass through: Region
        Columns to transform:
            Year1
            Year2
            Year3
            Year4
        Category column name: Year
        Category column data type: String
        Value column name: Value
        Value column data type: Integer
        Empty values included: No

Then then calculate the value for each year value using an OVER function in an expression like:

Count() over ([Value])

Option 2) Use a regular expression function like RXReplace() in a calculated column:

Len(RXReplace(RXReplace(Concatenate([Year1]),String([Year1]),"$","g"),"[^$]","","g")) 
+ Len(RXReplace(RXReplace(Concatenate([Year2]),String([Year1]),"$","g"),"[^$]","","g")) 
+ Len(RXReplace(RXReplace(Concatenate([Year3]),String([Year1]),"$","g"),"[^$]","","g")) 
+ Len(RXReplace(RXReplace(Concatenate([Year4]),String([Year1]),"$","g"),"[^$]","","g"))

Explanation: First concatenate the values in each year column, then search for the Year1 value in that concatenated set. If the Year1 value exists then replace it with a $ character (or other unique single character not present in the values) for all occurrences. Then perform the replace operation again where everything except $ is replaced with the empty set "". At this point the string is only left with $ characters, one for each occurrence, which can be easily counted. Use the Len() function to sum it up across each column to get total year value occurrence. Doc: Text functions

https://docs.tibco.com/pub/sfire-analyst/7.11.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_text_functions.htm?rhhlterm=text%20functions&rhsyns=%20

Related articles