count and group by

Eight Stars

count and group by

Hi Talend experts

 

I need to perform something like this:

 

I need to make Fact table which shows how many students visited and posted comments on portal.
 
The fact table structure would be like this:
 Student_key, region_key, disability_key, date_key, portal_page_view_count, portal_comment_count
 
I have got Portal_event table which will have data like this:
 
id, user_id, name (i need to capture where name='posted_comment' and where name='page_visited') and these counts will go to page_view_count and portal_comment_count in fact table
 
id .  User_id .  Count
1 .    100 .        1
2 .    100 .        1
3 .     200 .       1
4 .     300 .       1
5 .      300 .      1
 
based on such data i will get count for page_visited student count and posted_comment count.
 
 Another table is portal_user_history which captures which students logged into portal 
id, studentid 
 
Basically based on join of id.portal_event=id.portal_user_history i will get studentid which i will pass it to next one to next component.
 
Then I get Student_key etc later...
 
 But mainly how do i organise getting studentid detail from both table portal_event and portal_user_history table and pass it to next flow?
 
Thanks
Harshal.
Highlighted
Six Stars

Re: count and group by

Can you please provide sample datasets that we can use to simulate scenarios you posted?

 

It's a bit hard to follow at this point, at least for me.

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.