Talend Data integration Uniting 2 rows into one

Highlighted
Four Stars

Talend Data integration Uniting 2 rows into one

Hello,

I'm currently in the process of doing an ETL process and during it i've encountered an obstacle that i can't quite find a solution for it. So basically my datasets are from a .csv file and im loading them into my datawarehouse, my fact table has as its composite primary key "schoolID" "courseID" "locationID" and as facts "totalMale" "totalFemale" and "total". However on the .csv file i have 2 rows that have the same primary key but different values for "genre" and "totalRegistered", I would like to unite those 2 rows into a single one that has both totalMale and totalFemale as shown in the picture bellow. I have no idea how to do it right now as I'm quite new to the software, any suggestions?Screenshot_1.png


Accepted Solutions
Highlighted
Sixteen Stars
Sixteen Stars

Re: Talend Data integration Uniting 2 rows into one

Separate M from F using tFilterRow then compute (sum) total per genre with tAggregateRow giving 2 linked tHashOutput.

Join both tHash rows using a tMap with appropriate filter on genre for each and sum gendered total giving gran total.

Looks like this:

job.png

 tAggregate are identicals:

job.png

And tMap is like this:

job.png

Here is the final result:

[statistics] connecting to socket on port 3832
[statistics] connected
.--------+--------+----------+---------+-----------+-----.
|                       tLogRow_14                       |
|=-------+--------+----------+---------+-----------+----=|
|schoolId|courseId|locationId|totalMale|totalFemale|total|
|=-------+--------+----------+---------+-----------+----=|
|1       |2       |3         |40       |30         |70   |
'--------+--------+----------+---------+-----------+-----'

[statistics] disconnected

TRF

View solution in original post


All Replies
Highlighted
Sixteen Stars
Sixteen Stars

Re: Talend Data integration Uniting 2 rows into one

Separate M from F using tFilterRow then compute (sum) total per genre with tAggregateRow giving 2 linked tHashOutput.

Join both tHash rows using a tMap with appropriate filter on genre for each and sum gendered total giving gran total.

Looks like this:

job.png

 tAggregate are identicals:

job.png

And tMap is like this:

job.png

Here is the final result:

[statistics] connecting to socket on port 3832
[statistics] connected
.--------+--------+----------+---------+-----------+-----.
|                       tLogRow_14                       |
|=-------+--------+----------+---------+-----------+----=|
|schoolId|courseId|locationId|totalMale|totalFemale|total|
|=-------+--------+----------+---------+-----------+----=|
|1       |2       |3         |40       |30         |70   |
'--------+--------+----------+---------+-----------+-----'

[statistics] disconnected

TRF

View solution in original post

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog