Hi,
i need to get sum of data in join table and keep it in Fact table, for that i have used below code
SIGNUPACTIVITYLOG:
LOAD `invitation_id`,
`user_id`,
`client_id`,
`batch_meta_data_id`,
`referrer_email_type_id`,
date_accessed,
`user_signed`;
SQL SELECT
`invitation_id`,
`client_id`,
`user_id`,
`batch_meta_data_id`,
`referrer_email_type_id`,
`user_signed`,
date_accessed
FROM MyDB.SIGNUPACTIVITYLOG;
left join (SIGNUPACTIVITYLOG)
LOAD
`invitation_id`,
`lender_offer_id`,
`final_test_status`;
SQL SELECT
`user_id`,
`batch_meta_data_id`,
`invitation_id`,
`lender_offer_id`,
`final_test_status`
FROM MyDB.COURSECOMPLETION;
Final:
LOAD `invitation_id`,
`user_id`,
`client_id`,
`batch_meta_data_id`,
`referrer_email_type_id`,
`user_signed`,
`lender_offer_id`,
`final_test_status`,
date_accessed,
Count(date_accessed) as LandingCount
Resident SIGNUPACTIVITYLOG Group by client_id,batch_meta_data_id,referrer_email_type_id, invitation_id,user_id,final_test_status,user_signed,lender_offer_id, date_accessed;
drop Table SIGNUPACTIVITYLOG;
So in final table i have count of date_accessed for particular referrer_email_type_id with respective to client . Now, if i put straight table using dimension like client_id, batch_meta_data_id , referrer_email_type_id and LandingCount (put as dimension). Here i am not getting total count for each referrer_email_type_id with respect to client_id, batch_meta_data_id. What i am doing wrong.
let me know if you need more info on this? PLease help me on this
