Find_ranges_of_country_codes

Highlighted
Five Stars

Find_ranges_of_country_codes

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.

range_excel.png

 

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:

talend_job.png

 

output:

range_talend.png

 

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

 


Accepted Solutions
Nine Stars

Re: Find_ranges_of_country_codes

simple job

Regards
Abhishek KUMAR

All Replies
Nine Stars

Re: Find_ranges_of_country_codes

As your input it correctly sorted on plz.

You could use tAggregartesortedrow.

 

input --> tAggregartesortedrow --> output

 

Talend.JPG

 

get correct number of input record count.

 

Let me know if it helped

Regards
Abhishek KUMAR
Five Stars

Re: Find_ranges_of_country_codes

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:

talend_out.png

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:

talend_out_should.png

Thanks a lot,

Olli

 

 

Nine Stars

Re: Find_ranges_of_country_codes

simple job

Regards
Abhishek KUMAR

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads