Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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.
@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