Find_ranges_of_country_codes

[olni]

Hello, I'am new/novice with Talend and i have a - maybe - simple  problem:

I have the following excel file, with the wanted result on the right.

I sorted the file asc to the country-codes "PLZ". The VNr are people that are responsible for the specific area.
It could be, that a "VNr" has multiple ranges and/or even a single one. (see picture above)

All columns are formatted as string.

I build the following job:

output:

As you can see i managed to detect the start_plz and the end_plz of the range and also the beginning of the next range, but the start_plz/end_plz ranges are not at the right place.The "1936" is correct,  but not as the end_plz range of "80049". It would be nice, if someone can give me a hint how to rearrange the matches to get them to the right place.
thanks a lot,

Olli

[akumar2301]

As your input it correctly sorted on plz.

You could use tAggregartesortedrow.

input --> tAggregartesortedrow --> output

get correct number of input record count.

Let me know if it helped

[olni]

Hi Abhishek, thanks for your reply!

Unfortunately your suggestion gives not the expected result, but it is a  hint, what the way could be.

I modified my job slightly and added a rowcounter and changed the start_plz so that you can see all the plz in between.
the result ist this:

I was not able to "move" End_PLZ to the desired position with tAggregateSortedRow any hint would be very helpful.

Here my Expression to find the "Start_PLZ":

(Var.VNr_last == null || Var.VNr_last.equals("")) ? Var.PLZ_this
 :(Var.VNr_last.equals(Var.VNr_this))?Var.PLZ_this="":Var.PLZ_this

And here for detecting the "last" PLZ of the range before(!) which i do with tMemorizeRow Component

(Var.VNr_last == null || Var.VNr_last.equals("")) ? "" :(Var.VNr_last.equals(Var.VNr_this))?row3.PLZ="":((((String[]) globalMap.get("tMemorizeRows_1_PLZ"))[1]))

I want to have this result:

Thanks a lot,

Olli

[akumar2301 · Accepted solution]

simple job

simplejob_0.1.zip