Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

SUMM ALL VALUES WHOSE CONTAINS "abc"

Highlighted
Six Stars

SUMM ALL VALUES WHOSE CONTAINS "abc"

Hello,

 

I have a sheet which contains Name, Location and Time in minutes

I want to sum all value which contains for example "rec" location

image.png

The result for example should be

image.png

I was trying to make InputExcel then a Tmap with expression (row1.Location.contains("REC"), but im getting null pointer exception

 

Can someone help me?


Accepted Solutions
Highlighted
Eleven Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

try this

 

Talend.JPG

 

Talend2.JPG

 

Sort on Location .

Note : I forgot to do null check in tJavaRow.

 

Result

[statistics] connected
FOR|40
HO|60
JDS|40
MAN|100
RBO|120
REC|60
[statistics] disconnected

Regards
Abhishek KUMAR

View solution in original post


All Replies
Highlighted
Eight Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

This means row1.Location is nullable. You can do something like row1.Location !=null && row1.Location.contains("REC").

I'd add an additional column which will be the location key, with values REC/DSV/KLM,... then use a tAggregateRow based on this.
Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

I tried this, the error stops but nothing is shown by tlogrow, just empty lines

 

image.png

 

image.png

 

About using tAgregateRow using Location is not fullfit for my job because in some Cases Locations Is not the same

For Example: XXXX REC, YYYY REC, 1111 REC

 

 

Highlighted
Eleven Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

 

tFileExcel ,

connect to tFilterRow ( Use advance Option) Condition  row1.Location !=null && row1.Location.contains("REC")

Connect to tJavaRow , add additional Column hardcode the value "REC"

connect to tAggregateRow , Group on new column created above

Output on Location as 1st , and NUmber_aten as Sum.

 

you will get 1st record as Location and sum of all REC.

 

 

 

Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

Could you help me in Tjavarow in this case "add additional column hardcore"
Highlighted
Eight Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

If location is not the same, ie. "XXXX REC" and "YYYY REC" should not be grouped, then the String.contains is not a fit.

Okay, then I suggest a tJavaFlex. Something like the following (just writing from the browser, there maybe mistakes).

Assuming input/output flows are called respectivly "Input", "Output"
Start:
Map<String,Integer> locations = new HashMap<>();

Main:
// skip unparseable input

// count
if (locations.containsKey(Input.Location)) {
locations.put(Input.Location, locations.get(Input.Location) + Input.Count);
} else {
locations.put(Input.Location, Input.Count);
}

END:
Save to global variable

After that you can generate an output flow from this variable using another tJavaFlex
Highlighted
Eleven Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

in Schema , output side add a column e.g. dummy

 

In main without 

output_row.dummy="REC"

Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

I really dunno understand what you did.
But i have some arguments:
Of course we can use string Contains for "REC", it will be always have this Name in location "REC" but what cames before or after may change

Im trying
TInput ->filter->tJavarow-tlog

But in tJavarow i have to increment the values
because if i only filter and tlog, i get ALL the Locations With REC (15, 43, 15,..) and now i have to sum all of them and the job will work properly, but i didnt notice or know how to make this sum like
a += b
Highlighted
Moderator

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

@iansoares ,please find the attached job,which was working for your scenario.Untitled.png

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

No man, that isnt what i want
the Output should sum the previous values
But i cant use Aggregate because in some cases
xxxx DSV, yyyy DSV, XTZE DSV..
andsum when contains DSV and values
Highlighted
Eleven Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

Can you send me a sample file and expected output
Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

Have u checked your inbox? I Already Sent there.
Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

@akumar2301 Input

AssuntoTempo total gasto
ID:00010 (Subtarefa) - KPQ - PLANTÃO - MAN - DISPLAY- DT60
ID:00005 (Subtarefa) - KPQ - PLANTÃO - HO - ROTINA MATINAL - 02/02/2019 - DT60
ID:00023 (Subtarefa) - KPQ - PLANTÃO - RBO - DISPLAY  - DT120
ID:00026 (Subtarefa) - KPQ - PLANTÃO - JDS - LINK da Projeção  - DT40
ID:00029 (Subtarefa) - KPQ - PLANTÃO - FOR - cartão TEF - DT40
ID:00347 (Subtarefa) - KPQ - PLANTÃO - MAN -  servidor Arena - DT40
ID:41432 (Subtarefa) - KPQ - PLANTÃO - REC - Impressora  - DT60

 

 

Why i should expect in output

Location CodeTempo total gasto
MAN100
HO60
RBO120
JDS40
FOR40
REC60
Highlighted
Eleven Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

try this

 

Talend.JPG

 

Talend2.JPG

 

Sort on Location .

Note : I forgot to do null check in tJavaRow.

 

Result

[statistics] connected
FOR|40
HO|60
JDS|40
MAN|100
RBO|120
REC|60
[statistics] disconnected

Regards
Abhishek KUMAR

View solution in original post

Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

Im getting error in TJavaRow
Exception in component tJavaRow_6 (project)
java.lang.ArrayIndexOutOfBoundsException: 3
Highlighted
Eleven Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

Looks like some of input are not in correct format as you specified in
sample.

Error means your input doesnot have three “-“ in it.
Regards
Abhishek KUMAR
Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

No man, the input is the same i sent you, only works when entering [0], i really dunno why this is happening, i will try to use substring 

Highlighted
Six Stars

Re: SUMM ALL VALUES WHOSE CONTAINS "abc"

Nevermind, i found my error, in the final of data was a line which does not have "-" and this line was getting out of bounds

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog