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|
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.