Sum from different criteria

Six Stars

Sum from different criteria

I didn't manage to find a good subject name so let me explain in details :

fields.PNG

I have those fields. As you can see there are client, service and time.

What i want is to get for each client :

- the time total  (sum of all time for the same client )

- time for each service

I managed to get the total time for each client with a tAggregateRow but now i can't figure out how to get the time for each service.

 

Example of what i want :

|Client  |TotalTime|Service1|Service2|Service3|

|ClientA|150          |45          |50           |55         |

|ClientB|99            |33          |33           |33         |

 

I don"t know how i can get all the service, place them in the colum name and get the sum of the time by client for every service.

 

If it's not clear enough, ask me.

Thank you in advance !


Accepted Solutions
Six Stars

Re: Sum from different criteria

Hi,

 

We have many ways to do your requirement but i did it as  follows.

 

job.PNGfixedflowinput3.PNG

 

sortrow.PNGaggregaterow.PNGhashinput.PNGtmap.PNGpivot.PNG

 

--

Please give Kudos and mark topics as solved where appropriate.

View solution in original post


All Replies
Six Stars

Re: Sum from different criteria

Hi,

 

Please provide 2 or more records of your input data and its corresponding output.

Six Stars

Re: Sum from different criteria

Input data :

First col : Client;

Second : Service;

Third : Time.

data.PNG

As you can see, for one client there are different services and time corresponding.

 

So the output for the client RESAMANIA/STADLINE would look like :

|Client                            |TotalTime|IMPLEM-01|IMPLEM-03|

|Resamania/STADLINE|180           |60               |120              |

 

Tell me if you need more information.

Thank you anyway  hpamidipola !

 

Six Stars

Re: Sum from different criteria

Hi,

 

We have many ways to do your requirement but i did it as  follows.

 

job.PNGfixedflowinput3.PNG

 

sortrow.PNGaggregaterow.PNGhashinput.PNGtmap.PNGpivot.PNG

 

--

Please give Kudos and mark topics as solved where appropriate.

View solution in original post

Six Stars

Re: Sum from different criteria

Hi hpamidipola,

 

Thanks for trying to help me !

Unfortunately, i can't find the components tHashOutput/Input on my talend version.

I checked on Talend Exchange also and i didn't find them neither. 

Do you have a link for me to DL it ? Else, is there another way to do it, maybe without that component. 

 

Thank you in advance Smiley Happy

Six Stars

Re: Sum from different criteria

Hi,

 

1: On the main menu navigate to File –> Edit Project properties
2: Select Designer –> Palette Settings
3: Go to technical –> select tHashInput/tHashOutput and press the right arrow.

 

If you have more data please use file/DB components instead of hash components. The reason why i used the hash components is, i considered less data(only 3 records).

 

--

Please give Kudos and mark topics as solved where appropriate.

Six Stars

Re: Sum from different criteria

I actually have more rows (5000-6000) . I receive that data from a tRest (post request). What can you advise me to use to do it ? 

Six Stars

Re: Sum from different criteria

Hi,

 

You can use hash components. 

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch 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