finding if n out of m columns are null over each row using calculated column functions in Spotfire

Question

I have the following table and I would like to get the number of nulls for each SEQ_ID

SEQ_ID   zScore for 7d  zScore for 14d  zScore for 21d  zScore for 28d  zScore for 35d
456        11.353           13.2922         9.0162           8.8533
789        8.5991                            8.8244                           5.7394    

So for SEQ_ID 456 I would have 1 null For SEQ_ID 789 I would have 2 nulls

Is there a way to do this without writing complicated case statements with brute force combinations in the Calculated column area using Spotfire

Answer 1

I guess you are looking for a Spotfire custom expression not involving R.

This would give you the number of columns that are not null. If you know the total number of columns, you can easily turn it into the number of null columns

Len(RXReplace(Concatenate($map("[yourtable].$esc($csearch([yourtable],"*"))",",'-',")),'\\w+','Z','g')) -
Len(RXReplace(Concatenate($map("[yourtable].$esc($csearch([yourtable],"*"))",",'-',")),'\\w+','','g'))

[yourtable] would be the name of your data table. This acts on all columns.