Five Stars

Add rowNumber similar as SQL DENSE_RANK()

There is a flat file with CustomerID and Sales column. How can I add the RowNum column like below (similar as SQL Dense_Rank() )for each CustomerID? 

I tried to use tJavaFlex, but was unable to get the Dense Rank. Thank you very much.

CustomerIDsalesrowNum
1425630.341
1425630.341
1425630.322
1425693
1456745.31
1456743.22
14567413
14567354
14567354
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Twelve Stars

Re: Add rowNumber similar as SQL DENSE_RANK()

Here's a way you could approach this using a tJavaFlex. I have built an example job using your data. The job looks like below....

Screen Shot 2017-09-07 at 22.28.06.png

 

The tFixedFlowInput simply holds your example data. This can be seen below....

Screen Shot 2017-09-07 at 22.28.21.png

The tSortRow is simply there to ensure the order is correct. The sort key is CustomerID, then Sales. This is seen below....

Screen Shot 2017-09-07 at 22.28.46.png

Once the data is ordered, we get on with the dense rank code. This uses a bit of Java, but it is quite simple. This can be seen in the tJavaFlex below.....

Screen Shot 2017-09-07 at 22.29.16.png

 

I have included the code below so that you can copy it.....

 

Start Code

// start part of your Java code
String curCustomerID = null;
double lastValue = 0.0;
int rank = 0;

Main Code

 

// here is the main part of the component,
// a piece of code executed in the row
// loop
if(curCustomerID==null || curCustomerID.compareToIgnoreCase(row2.CustomerID)!=0){
	curCustomerID = row2.CustomerID;
	lastValue = -99999.9;
}

row3.CustomerID = curCustomerID;

if(row2.Sales!=lastValue){
	lastValue = row2.Sales; 
	rank = routines.Numeric.sequence(curCustomerID, 1, 1);
}

row3.Sales = lastValue;
row3.Rank = rank;

Essentially what we do is set up some variables to keep track of values between rows in the Start Code section. We then use the Main Code section to identify when the CustomerID has changed and when the Sales value has changed. When either have changed, we call the "Sequence" routine using the CustomerID as the key. When there are no changes, we reuse the last "rank" value.

 

 

 

Rilhia Solutions
3 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Add rowNumber similar as SQL DENSE_RANK()

Using tJavaRow you can:
- compare the current ID with the previous one memorized in a global variable (initialize to a specific value when the job starts)
- if the value changes, get the next value for the sequence s1 and store the result into the global variable "dr" using the syntax globalMap.set("dr", Numeric.sequence("s1", 1, 1))
- push the value of "dr" to the field rownum with output_row.rownum = (Integer)global Map.get("dr")

Hope this helps.

TRF
Highlighted
Twelve Stars

Re: Add rowNumber similar as SQL DENSE_RANK()

Here's a way you could approach this using a tJavaFlex. I have built an example job using your data. The job looks like below....

Screen Shot 2017-09-07 at 22.28.06.png

 

The tFixedFlowInput simply holds your example data. This can be seen below....

Screen Shot 2017-09-07 at 22.28.21.png

The tSortRow is simply there to ensure the order is correct. The sort key is CustomerID, then Sales. This is seen below....

Screen Shot 2017-09-07 at 22.28.46.png

Once the data is ordered, we get on with the dense rank code. This uses a bit of Java, but it is quite simple. This can be seen in the tJavaFlex below.....

Screen Shot 2017-09-07 at 22.29.16.png

 

I have included the code below so that you can copy it.....

 

Start Code

// start part of your Java code
String curCustomerID = null;
double lastValue = 0.0;
int rank = 0;

Main Code

 

// here is the main part of the component,
// a piece of code executed in the row
// loop
if(curCustomerID==null || curCustomerID.compareToIgnoreCase(row2.CustomerID)!=0){
	curCustomerID = row2.CustomerID;
	lastValue = -99999.9;
}

row3.CustomerID = curCustomerID;

if(row2.Sales!=lastValue){
	lastValue = row2.Sales; 
	rank = routines.Numeric.sequence(curCustomerID, 1, 1);
}

row3.Sales = lastValue;
row3.Rank = rank;

Essentially what we do is set up some variables to keep track of values between rows in the Start Code section. We then use the Main Code section to identify when the CustomerID has changed and when the Sales value has changed. When either have changed, we call the "Sequence" routine using the CustomerID as the key. When there are no changes, we reuse the last "rank" value.

 

 

 

Rilhia Solutions
Twelve Stars

Re: Add rowNumber similar as SQL DENSE_RANK()

@TRF I left this question open for a while while knocking up my example and doing something else. Didn't mean to steal your thunder mate

Rilhia Solutions