Count all occurences of a certain value per column (other than Value Counter node)

KNIME Analytics Platform
1

Hi,

I do have a large table with 4M rows and 200 columns and want to count all occurences of a certain numeric value X for all columns. The desired output is:

 

ROW ID No. occurences X
ColumnName1 120
ColumnName2 0
ColumnName3 45

...

 

My current solution:

1. Use column list loop

    - Use Rule Engine to replace all missing values with -1

    - Use Rule Engine to replace all occurence of X with missing value

2. Transpose table

3. Use column aggregator and "count missing values"

 

This does give me the desired output but is incredibly slow (20h computation time). Value Counter node is also very slow and Statistics node doesn't work. Since the input table changes in structure over time I do need a generic solution (i.e. no manual column selection).

Is there a better solution for this problem? I am glad for any help at this point, I have tried numerous different ways but to no avail.

2

Can you just pass your table in to the Statistics node (use wildcard/regex column selection, pattern '*') and get the information you need out of the 'Occurrences Table' output?

3

Not really since I am dealing with numeric columns which can have an arbitrary number of unique values but in the Statistics node you have to define a maximum number of unique values for each column.