I have a little problem grouping by columns. My data looks like this:
col1
col2
A
10
B
20
B
10
B
5
B
30
C
60
D
20
D
35
D
65
D
30
E
50 and so on..
I would like to take the minimum of col2 values by grouping the col1 values. However for more than 2 same values in col1, I want to have them grouped in 2's. My answer would look like:
col1
col2
A
10
B (first minimum)
10
B (second minimum)
5
C
60
D (first minimum)
20
D (second minimum)
30
E
50
I've been trying to do this using the java snippet and no luck so far. Any help is much appreciated!!
I would just GroupBy col1 and compute the min and max values of col2. After that, you can combine (aggregate) the two columns as a set and finally Ungroup the resulted column. I think this would create a table similar to your expected, probably the col1 content would be less detailed (which can be fixed with if you retain the number of values in the set too and use a Java Snippet for example).
I don't think that would solve it, but a variation should:
GroupBy, computing min and a set; Ungroup; Split by value=min; GroupBy on second port, computing min; Concatenate.
Sprinkle in Constant Value Column's if you want to tag first and second mins differently. If you also want to tag solo-mins and lower pair-mins differently, you might need something more. Rule Engine's, a Reference Row Filter... there are a lot of ways.