How to report the column name from column scores

KNIME Analytics Platform
1

Hi,

I have an interesting problem I am not able to fathom out how to do it. To give some background first, the full table is likely to be about 20 columns and 200000 rows. The data input could change such that the number of rows may change but the column structure stays unchanged. 

So the problem is, I have many IDs which are assessed by various categories A, B, C etc and scored against these categories from 0 to 1. I then want to report back the lowest score observed, and which category(s) gave the lowest score. Now I know how to report back the lowest score observed using column aggregator node on Cols A-Z and chose Minimum as aggregation method, but am unsure how to get another column reporting back the column name(s) which gave the lowest score in an efficient way. So an example is:

ID.  ColA.  ColB.  ColC.  ColD.  ColE

1.       0.1.     0.5.     0.7.      0.2.     0.3

2.       0.9.     0.8.     0.4.      0.4.     0.5

3.      0.6.       0.5.     0.3.      0.8.    1.0

should return these extra columns

ID.     MinScore.      MinCategory

1.             0.1.                 A

2.             0.4.                 C, D

3.             0.3.                 C.

 

ideas anyone.

simon.

2

I know it is not an easy option for 20 columns (and only returns the smallest index of the extreme values), but the Math Formula node's colmin, colmax functions might help in your case.

Cheers, gabor

Sorry, in the previous version I used argmin and argmax, but colmin and colmax are better fit in this case.

3

Hi Gabor,

Thanks, I understand this now. It took me a few minutes, its giving me the column Index. This is most useful, and has turned out to be easier than I thought it was going to be.

However, it only reports one column index, how can I get two column indexes when there is a tie ?

Many thanks,

Simon.

4

Here is an alternate way to do it based on the unpivot and groupby nodes. If performanace is more important than readability I would probably write a java snippet.  

5

Very nice Aaron, you made light work of that. This will be really useful. I need to learn to use these Pivot nodes more often

Thanks,

 

Impressed!

Simon.