count and group by

[Parikhharshal]

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.

[Anonymous]

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.