Get the Highest Value from an Certain String

[Anonymous]

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 

https://www.tutorialspoint.com/java/number_max.htm .. if so how i have to integrate this  in talend ? 

best regards john 

[TRF]

Hi,

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

[Anonymous]

Hello TRF,

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

Situation: 

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

Problem: 

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 

[Evandao]

Hello,

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.

[Anonymous]

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.

Notations:

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 

[Anonymous]

Hello again,

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

Approach: 

Description:

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.

Example: 

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 

[Anonymous]

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

[Anonymous]

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 ? 

@Evandao:

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 

[Anonymous]

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.

[Anonymous]

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