topic Re: Add rowNumber similar as SQL DENSE_RANK() in Talend Studio

Add rowNumber similar as SQL DENSE_RANK()

JaneYu — Thu, 07 Sep 2017 21:26:54 GMT

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.

CustomerID	sales	rowNum
14256	30.34	1
14256	30.34	1
14256	30.32	2
14256	9	3
14567	45.3	1
14567	43.2	2
14567	41	3
14567	35	4
14567	35	4

Re: Add rowNumber similar as SQL DENSE_RANK()

TRF — Thu, 07 Sep 2017 21:56:41 GMT

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.

Re: Add rowNumber similar as SQL DENSE_RANK()

Anonymous — Thu, 07 Sep 2017 22:42:48 GMT

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

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

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

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

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.

Re: Add rowNumber similar as SQL DENSE_RANK()

Anonymous — Thu, 07 Sep 2017 22:44:29 GMT

@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