Veys
May 26, 2016, 7:09am
1
Hi all,
I am trying to create a ranking, but within a row.
I have following table
Col1
Col2
Col3
Col4
Col5
Col6
Hans
10 EUR
12 EUR
15 EUR
22 EUR
9 EUR
Bert
7 EUR
5 EUR
11 EUR
4 EUR
2 EUR
And basically looking for following output
Col1
Col2
Col3
Col4
Col5
Col6
Hans
4
3
2
1
5
Bert
2
3
1
4
5
How do you derive the data in the second table from the first ?
A bit tricky since the Rank node is designed to work by columns. Without reverting to any R code snippet or code based solutions, your best option is to Transpose, Rank, Transpose again and clean up.
See the attached workflow for an example on how to achieve this.
These are the ranks of each row. Highest EUR amount --> Rank 1, second highest --> Rank 2 and so on.
Geo
May 27, 2016, 6:37pm
5
unpivot (retain Col1 as constant), rank, pivot
unpivoting, then rank and re-pivot is right, there are few things to be wary when doing it. You need to convert the "10 EUR" etc strings into numbers, and pivoting will change the names of the columns, Also to guarantee you end up back in the same row order, you may need to resort.
The attached workflow does what you want for your sample.
Veys
June 27, 2016, 8:27am
7
Hi all,
thanks for your replies and suggestions, much appreciated!
I ended up using the Java Snippet node with following code, which works fine.
Possibly not the best code, but I want to share it anyway:
// system imports
import org.knime.base.node.jsnippet.expression.AbstractJSnippet;
import org.knime.base.node.jsnippet.expression.Abort;
import org.knime.base.node.jsnippet.expression.Cell;
import org.knime.base.node.jsnippet.expression.ColumnException;
import org.knime.base.node.jsnippet.expression.TypeException;
import static org.knime.base.node.jsnippet.expression.Type.*;
import java.util.Date;
import java.util.Calendar;
import org.w3c.dom.Document;
// Your custom imports:
import java.util.Arrays;
import java.util.Comparator;
// system variables
public class JSnippet extends AbstractJSnippet {
// Fields for output columns
/** Output column: "BL Rank" */
public Double out_BLRank;
/** Output column: "GR Rank" */
public Double out_GRRank;
/** Output column: "LG Rank" */
public Double out_LGRank;
/** Output column: "AC Rank" */
public Double out_ACRank;
/** Output column: "DR Rank" */
public Double out_DRRank;
/** Output column: "SK Rank" */
public Double out_SKRank;
/** Output column: "MT Rank" */
public Double out_MTRank;
// Your custom variables:
int i;
// expression start
public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:
final double[][] mySalesArray = {
new double[] {71.0,c_BL,.0},
new double[] {72.0,c_GR,.0},
new double[] {73.0,c_LG,.0},
new double[] {74.0,c_AC,.0},
new double[] {76.0,c_DR,.0},
new double[] {78.0,c_SK,.0},
new double[] {82.0,c_MT,.0}
};
Arrays.sort(mySalesArray, new Comparator<double[]>() {
@Override
public int compare(final double[] o1, final double[] o2) {
final double d1 = o1[1];
final double d2 = o2[1];
return Double.compare(d1, d2);
}
});
i=7;
for(final double[] d:mySalesArray) {
d[2] = i;
i--;
}
i=7;
for(final double[] d: mySalesArray) {
if(d[0] == 71.0 ) {
out_BLRank = d[2];
if(d[1]==0){
out_BLRank = .0;
}
}
if(d[0] == 72.0 ) {
out_GRRank = d[2];
if(d[1]==0){
out_GRRank = .0;
}
}
if(d[0] == 73.0 ) {
out_LGRank = d[2];
if(d[1]==0){
out_LGRank = .0;
}
}
if(d[0] == 74.0 ) {
out_ACRank = d[2];
if(d[1]==0){
out_ACRank = .0;
}
}
if(d[0] == 76.0 ) {
out_DRRank = d[2];
if(d[1]==0){
out_DRRank = .0;
}
}
if(d[0] == 78.0 ) {
out_SKRank = d[2];
if(d[1]==0){
out_SKRank = .0;
}
}
if(d[0] == 82.0 ) {
out_MTRank = d[2];
if(d[1]==0){
out_MTRank = .0;
}
}
}
// expression end
}
}