topic Aggregation on one attribut and keeping date in Talend Studio

Aggregation on one attribut and keeping date

Slope — Tue, 10 Mar 2020 16:04:12 GMT

Hello,

I already read and learn a lot because of you (the community) and that's my first post.

I'm facing a wall implementing a workflow, here's my problem.

First, let's have a look on data and what I've (sorry I anonymised data):

Col1,2,3 and DATE_START are grouped with a tAggregateRow, I've added "type" column to help on the comprehension of the need but it'll disappear at the end.

The problem is, I would like to keep that format (without "type") but get sum on the C1/C2/C3 column, to have this kind of results :

I determine the value on C1/C2/C3 column within a tMap, here's an example :

!Relational.ISNULL(row10.TYPE)&& row10.TYPE=="C"?1:0 and then set C1 column value with the result

What can be imagine to perform the need of that workflow ? I tried a lot of things but now I looking for new ideas .

Thanks by advance,

Kind regards,

Slop

Re: Aggregation on one attribut and keeping date

Anonymous — Wed, 11 Mar 2020 16:24:10 GMT

Hi,

You want to perform a counter to your C1/2/3 columns

I suggest you to declare 3 global variables in start of job

globalMap.put("C1Count",0);
globalMap.put("C2Count",0);
globalMap.put("C3Count",0);

In your tmap you will increment your variables and write them in your C1 2 3 columns.

Create 3 variables in tmap :

# first variable increments C1
row1.TYPE.equals("C1Count")?globalMap.put("C1Count",(Integer)globalMap.get("C1Count")+1):null
# second variable increments C2
row1.TYPE.equals("C2Count")?globalMap.put("C2Count",(Integer)globalMap.get("C2Count")+1):null
# third variable increments C3
row1.TYPE.equals("C3Count")?globalMap.put("C3Count",(Integer)globalMap.get("C3Count")+1):null

then write in your tmap output fields C1 C2 C3 respectivly :

(Integer)globalMap.get("C1Count")

(Integer)globalMap.get("C2Count")

(Integer)globalMap.get("C3Count")

Re: Aggregation on one attribut and keeping date

Slope — Wed, 11 Mar 2020 16:29:59 GMT

Hi,

Thanks for that first answer, I'm now able to increment value as intended but I've a new issue.

I need to increment (c1,c2,c3), but the increment have to be linked to col 1/2/3 (which are grouped values).

So my increment start for "AAA aaaa" values, and then when I switch to "BBB aaaa" values, need to be reset to start a new sequence of increment

Re: Aggregation on one attribut and keeping date

Anonymous — Wed, 11 Mar 2020 16:56:57 GMT

I will need a sample of Input and Output Data in order to see what exactly are the transformations desired

Re: Aggregation on one attribut and keeping date

Slope — Mon, 16 Mar 2020 07:27:36 GMT

Hello,

I haven't solved my problem but due to covid …. I can't work anymore so I'll keep you informed as soon as the access to my workstation is possible.

Kind regards

Re: Aggregation on one attribut and keeping date

Slope — Tue, 17 Mar 2020 15:56:07 GMT

Hello @tehLurdUfEngmer ,

I'm back and I was able to move forward, but still turning around a problem, here's the last one.

The example (input) :

Here's what I would like (output) :

So to resume, now I would like to :

- keep on the column ---> "2ND", the MAX from values on column "FIRS"

Really simple to do it on SQL, even in access ... but not on talend.

Keep the aggregation on "NAME, NAME2, Date_Month, DATE_START" and having a MAX on "2ND".

Re: Aggregation on one attribut and keeping date

Slope — Wed, 22 Apr 2020 16:22:00 GMT

Hello there, just to let you know I find a solution to solve that problem.
I mixed "last" and "this" value on tMap with a globalMap variable on a tjavarow.

Thanks for your help