topic Re: Get the Highest Value from an Certain String in Talend Studio

Get the Highest Value from an Certain String

Anonymous — Mon, 24 Apr 2017 14:03:39 GMT

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

Re: Get the Highest Value from an Certain String

TRF — Mon, 24 Apr 2017 14:15:49 GMT

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

Re: Get the Highest Value from an Certain String

Anonymous — Tue, 25 Apr 2017 11:53:38 GMT

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

Re: Get the Highest Value from an Certain String

Evandao — Tue, 25 Apr 2017 12:50:12 GMT

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.

Re: Get the Highest Value from an Certain String

Anonymous — Wed, 26 Apr 2017 10:25:24 GMT

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

Re: Get the Highest Value from an Certain String

Anonymous — Thu, 04 May 2017 14:15:51 GMT

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

Re: Get the Highest Value from an Certain String

Anonymous — Thu, 04 May 2017 15:45:29 GMT

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

Re: Get the Highest Value from an Certain String

Anonymous — Fri, 05 May 2017 10:04:08 GMT

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

Re: Get the Highest Value from an Certain String

Anonymous — Fri, 05 May 2017 12:37:53 GMT

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.

Re: Get the Highest Value from an Certain String

Anonymous — Tue, 09 May 2017 08:48:27 GMT

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

Re: Get the Highest Value from an Certain String

Anonymous — Wed, 10 May 2017 06:58:48 GMT

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.

Re: Get the Highest Value from an Certain String

Anonymous — Wed, 24 May 2017 07:39:23 GMT

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

Re: Get the Highest Value from an Certain String

Anonymous — Tue, 14 Aug 2018 13:52:40 GMT

Methods, method parameters, interaction and overloading