Aggregation on one attribut and keeping date

Highlighted
Five Stars

Aggregation on one attribut and keeping date

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

 

image.png

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 :

 

image.png

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 Smiley Happy.

 

Thanks by advance,

Kind regards,

Slop

Highlighted
Seven Stars

Re: Aggregation on one attribut and keeping date

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

 

 

 

Regards,
Pierre
Highlighted
Five Stars

Re: Aggregation on one attribut and keeping date

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

Highlighted
Seven Stars

Re: Aggregation on one attribut and keeping date

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

 

Regards,
Pierre
Highlighted
Five Stars

Re: Aggregation on one attribut and keeping date

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

Highlighted
Five Stars

Re: Aggregation on one attribut and keeping date

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

 

image.png

 

Here's what I would like (output) :

 

image.png

 

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

Highlighted
Five Stars

Re: Aggregation on one attribut and keeping date

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 Smiley Happy

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