Five 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 

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

 

best regards john 

 

 

11 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Get the Highest Value from an Certain String

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

 


TRF
Five 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.

 

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:

 

talend-job-opp-offer-updates.png

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.

 

innerjoin-accountid.png

 

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

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.

f1.PNGf2.PNG

 

 

 

Evando Souza
Five 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.

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

 

Relations-Account-OFFER-OPP.png 

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 

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

 

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 

 

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

 

Five 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 ? 

 

@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 

 

 

 

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. 

 

dataFlow.PNG

tJavaRow configuration,

tjavaRow.PNG

Schema of tJavaRow,

tJavaRowSchema.PNG

tAggregateRow Config,

tAggregateRow.PNG

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

Output.PNG

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.

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

Five 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