Get the Highest Value from an Certain String

Six Stars

Get the Highest Value from an Certain String

Talend Open Studio

Version:  6.3.1


Hello everybody


I there any possibilty to get only Records with an highest Value of an String.


For example.


Offers which  has the following  Records names:

1002-001, 1002-002,  1003,  1004-001,


And i only want the following Records:

1002-002, 1003, 1004


I have searched for some methods in java and I'm not sure if i could use these function for it .. if so how i have to integrate this  in talend ? 


best regards john 



Fifteen Stars TRF
Fifteen Stars

Re: Get the Highest Value from an Certain String


I suggest you to separate code (1002, 1003, 1004, ...) from subcode (001, 002, 001, null) for each row then, using tAggregateRow, you can group by code and get the max value for subcode (at least I believe it should work).


Six Stars

Re: Get the Highest Value from an Certain String

Hello TRF,


Okay i think i have to explain our approach a litte bit more in Detail to figure out the case.




ERP Instance: 

We have some offer updates  which will be written  from our ERP and renewed as 001 002 and so on.

Salesforce Instance:

These offers will be assigned with an Opportunity automatically. One Opportunity has one OFFER (1:1).


New Offer Updates won't be assigend to the serveral OPP's  in SF again.


Issue Approach with Talend to reassign the Updated OFFERS to the Opp's:



Angebote infor only Fortschreibungen:

Query with only Update Offers from SF.


Opportunity Lookup:

Opp Lookup with inner join with Angebote infor only Fortschreibungen on Account ID.




So how i have to set the taggregate row in place ?  and which component i need for an each row loop ?


So this my first approach.. but honestly i have problems to figure out where should i start and what component should be my master source.


best regards 

Five Stars

Re: Get the Highest Value from an Certain String


I'm trying to understand your problem to try to help.

In the stream row2, what is the field that receives the new values ​​you mentioned ("001", "002" an so on)? Is it "Account_c"? In other words, can the same opportunity have more than one occurrence of "Account_c" in this flow?

If so, do you need each opportunity to know which is the largest "Account_c" to then lookup with the "Opportunity" component?

If so, you need to include the "tAggregateRow" component as directed by our friend TRF.

I simulated the adjustment in the following images.

I hope I have helped.





Evando Souza
Six Stars

Re: Get the Highest Value from an Certain String


Hello Evando, 


Thanks you both for your Help 


I've rethinked about my source and my target and i think the  Account-ID for making an Match isn't the right approach.


So  let me try to explain the objects first.. may then someone could help me to find out the right Indicators for Building my Job.


Account ----> Lookup   -->   Opp. m:n ( but we use  1:n)

Account  -->  Master Detail  -->  Offers   1:n

Offer  Lookup -->  OPP m:n  ( but we use 1:1) 


First Offer Process:

Creation Process Opp in ERP to Salesforce with Offer  :   ERP Created  OFFER ( Examplename: 101230) --> Offer Upload to Salesforce --> An SF. Process Started to create an OPP related with an OFFER. ( Examplename: sony-101230)  


Offer Update:

Process: ERP Created Offer Update ( 101230-001) --> Offer Uploaded to Salesforce 

Whats missing?: New Offer Updates won't  reassigend to the related Opportunity  (examplename: sony-101230)


Here is also an Schema from SF. ( Note: Infor-Angebote = OFFER) : 



Approach in Talend:


1.  Get all Opp. with Offers and make an Matching Compare on the the Offers-Name and replace the old Offers on the Opp with the new ones. ( for example. 10200 replaced with  102-001 or with 102-002 . depends on how much offer updates are created from the ERP)

I think this is the best approach


2. Fetch all  Accounts which has Opp. with Offers and get only the Updates from them and match these to the related Opps.  

 ( this is my job which is metioned here)


I hope i was able to point out my desire or my thinkings about my job a little bit more clearely. 


best regards john 

Six Stars

Re: Get the Highest Value from an Certain String

Hello again,


Today i had time to think again about my approach and i've found another one i think.




Query all Quotes with offer-updates. Aggregate all offer Updates that only the highest number counts.

Rename these to the origin offer name. Match with origin offer name.


Quote Update Records : 101-001,101-002,102-001,102-002 -->  filterout  only 102-00x(max) for each row so. here is "101-002" and "102-002" --> Trim Quote.Name to  101 and 102  --> Match ( inner join) with the First Chars from the Opportunity Name in Salesforce  which is the Quote number (101 and 102).    


could this work ? .. how i can filter for each row according to an expression like "every chars before the "-" appears ( xxx-).


best regards john 


Seven Stars

Re: Get the Highest Value from an Certain String

try splitting the string using "-" as the delimiter.

Something like row.quoteNumber.split("-")[0] 

E.g. If quoteNumber is "101-002" then row.quoteNumber.split("-")[0] should give you "101".


Six Stars

Re: Get the Highest Value from an Certain String

Hello @ArvinRapt


Thanks for your Help.


The Split Method which youve metioned doesn't work in almost Cases but if you have an number like 100-001,100-002 he brakes on 2 records with the same name so i think i need an Sorting first that he can filter the lowercases and sort out these ones. ( in fact only 100-002 should be stay over).


Could i do this with the taggregate component which is  metioned before ? 




Account and Opportunity: 1:n

Offer and Opportunity: 1:1


btw. The Highest Account is not relevant in my use case atm. 


May there are better ideas to solve this but i'm not really a pro one either in Talend nor in java.


thanks all for help 


best regards john 




Seven Stars

Re: Get the Highest Value from an Certain String

How about using a combination of tJavaRow and tAggregateTable?

You can first split and get the sub code values in another column.

e.g: If row1.names contains "100-01", then get subcode value "100" into another column.


Once you fetch it into another column, you can use tAggregate Row to group by that column and fetch max value of the entire string.

Attaching Screenshots for your reference. 



tJavaRow configuration,


Schema of tJavaRow,


tAggregateRow Config,


 Here I used this input -> " 1002-001,1002-002,1003,1004-001,1005-008,1005-006 ", The output that I got was,


You can then remove the 'subCode' column using a tFilterColumn component.

Hope this helps.

You can play around with the datatypes to maybe make it more efficient. What I have shown above is a just a template which you can build upon.

Six Stars

Re: Get the Highest Value from an Certain String

Hello @ArvinRapt


Thanks for pushing me in the right way.

It seems a little bit more complicated as i guess at the beginning.


So i need additional Fields to pass through the Job.

The problem is, that the Taggregate component cuts out the additional rows which aren't processed from this  and then it cannot be resolved in the next step.


Short Outline:


field 1                                                              field 1

field 2           -in--> Taggregate row  --out-->   field 2  

field 3                                                            


I havent found anything to pass some rows without modification  through  tAggregate.


May i understand sth. in an wrong way.


best regards john 

Seven Stars

Re: Get the Highest Value from an Certain String

Well the additional fields need to be mentioned in either the group by operations or in the aggregate operations for them to be passed to the output.

So depending on the data, you can figure out on which columns 'group by' can be done and on which columns you can perform aggregations.

Six Stars

Re: Get the Highest Value from an Certain String

Hello @ArvinRapt


Thanks for help 


But atm. we have an bigger Problem in our Prod System which I've mentioned Here.


Moreover i will rethink over the job to solve this with some extra ID's which are created on Upload before.


May this is an better way of thinking.


But as i said, this is just in a test environment the other one not. 


best regards john  


Two Stars

Re: Get the Highest Value from an Certain String

Methods, method parameters, interaction and overloading


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables


How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration


Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now