Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
in scrpt i am Accumulating data to get sum og landing, signups and completions by using below script
SIGNUPACTIVITYLOG:
LOAD `invitation_id`,
`user_id`,
`client_id`,
`batch_meta_data_id`,
`comm_type_id`,
date_accessed,
`user_signed`;
SQL SELECT
`invitation_id`,
`client_id`,
`user_id`,
`batch_meta_data_id`,
`comm_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`,
`comm_type_id`,
`user_signed`,
`lender_offer_id`,
`final_test_status`,
date_accessed,
sum(if(LEN(date_accessed)>0 , 1,0)) as landing_1,
sum(IF([user_signed]='1',1, 0)) as signup_1,
sum(IF([final_test_status]='Passed',1,0)) as ccmpleted_1
Resident SIGNUPACTIVITYLOG Group by client_id,batch_meta_data_id,comm_type_id, invitation_id,user_id,final_test_status,user_signed,lender_offer_id, date_accessed ;
Mapping_table:
Mapping load batch_meta_data_id &'|'&invitation_id&'|'&comm_type_id as LandingHitsLookupKey, sum(landing_1)&'|'&sum(signup_1)&'|'&sum(ccmpleted_1) as userLandingHits Resident Final Group by batch_meta_data_id &'|'&invitation_id&'|'&comm_type_id;
FACTTABLE:
Load
`user_id`,
`invitation_id`,
`batch_meta_data_id`,
`comm_type_id`,
`client_id`,
SubField(ApplyMap('Mapping_table', batch_meta_data_id &'|'&invitation_id &'|'&comm_type_id),'|',2) as signups_2,
SubField(ApplyMap('Mapping_table', batch_meta_data_id &'|'&invitation_id &'|'&comm_type_id),'|',1) as landinghits_2,
SubField(ApplyMap('Mapping_table', batch_meta_data_id &'|'&invitation_id&'|'&comm_type_id),'|',3) as completions_2
Resident Final;
drop Table SIGNUPACTIVITYLOG;
drop Table Final;
but using above script i am not getting sum of signups_2, landinghits_2 and completions_2 by client_id, comm_type_id, batch_meta_data_id, user_id
can please anybody help me on this. why i am not able to aggregate data.
Try loading mapping table in two parts with a Preceding load
Mapping_table:
Mapping load batch_meta_data_id &'|'&invitation_id&'|'&comm_type_id as LandingHitsLookupKey, sum_landing_1&'|'&sum_signup_1&'|'&sum_ccmpleted_1 as userLandingHits ;
LOAD
batch_meta_data_id ,
invitation_id,
comm_type_id ,
sum(landing_1) as sum_landing_1,
sum(signup_1) as sum_signup_1,
sum(ccmpleted_1) as sum_ccmpleted_1
Resident Final
Group by
batch_meta_data_id ,
invitation_id,
comm_type_id ;
As looking your loading script you don't have the fields landing_1, signup_1, ccmpleted_1 in Final can you check this and try this in mapping load table or you can rename this fields in the FInal table
Mapping_table:
Mapping load batch_meta_data_id &'|'&invitation_id&'|'&comm_type_id as LandingHitsLookupKey,
sum(Landing)&'|'&sum(signedUser)&'|'&sum(CompletedUser) as userLandingHits Resident Final Group by batch_meta_data_id &'|'&invitation_id&'|'&comm_type_id;
No, even after preceding mapping table with load result remains same.
ok, then lets move the field LandingHitsLookupKey to the final table
SIGNUPACTIVITYLOG:
LOAD `invitation_id`,
`user_id`,
`client_id`,
`batch_meta_data_id`,
`comm_type_id`,
date_accessed,
`user_signed`;
SQL SELECT
`invitation_id`,
`client_id`,
`user_id`,
`batch_meta_data_id`,
`comm_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`,
`comm_type_id`,
batch_meta_data_id &'|'&invitation_id&'|'&comm_type_id as LandingHitsLookupKey,
`user_signed`,
`lender_offer_id`,
`final_test_status`,
date_accessed,
sum(if(LEN(date_accessed)>0 , 1,0)) as landing_1,
sum(IF([user_signed]='1',1, 0)) as signup_1,
sum(IF([final_test_status]='Passed',1,0)) as ccmpleted_1
Resident SIGNUPACTIVITYLOG Group by client_id,batch_meta_data_id,comm_type_id, invitation_id,user_id,final_test_status,user_signed,lender_offer_id, date_accessed ;
Mapping_table:
Mapping load LandingHitsLookupKey,
sum_landing_1&'|'&sum_signup_1&'|'&sum_ccmpleted_1 as userLandingHits ;
LOAD
LandingHitsLookupKey,
sum(landing_1) as sum_landing_1,
sum(signup_1) as sum_signup_1,
sum(ccmpleted_1) as sum_ccmpleted_1
Resident Final
Group by LandingHitsLookupKey;
HI Vineeth,
sorry to interrupt, just wanted to solve issue step by step, then found this
i want to know you this issue, which i found just now
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 rcdb.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 rcdb.COURSECOMPLETION;
NoConcatenate
finalTEDD:
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
Resident SIGNUPACTIVITYLOG ;
drop Table SIGNUPACTIVITYLOG;
NoConcatenate
finalTEDD1:
LOAD `invitation_id`,
`user_id`,
`client_id`,
`batch_meta_data_id`,
`referrer_email_type_id`,
`lender_offer_id`,
`final_test_status`,
date_accessed,
user_signed,
sum(IF([user_signed]='1',1, 0)) as signup_1
Resident finalTEDD Group by client_id,batch_meta_data_id,referrer_email_type_id, invitation_id,user_id,final_test_status,lender_offer_id, date_accessed,user_signed ;
drop Table finalTEDD;
NoConcatenate
finalTEDD2:
LOAD `invitation_id`,
`user_id`,
`client_id`,
`batch_meta_data_id`,
`referrer_email_type_id`,
`lender_offer_id`,
`final_test_status`,
date_accessed,
user_signed,
sum(signup_1) as signup_2
Resident finalTEDD1 Group by client_id,batch_meta_data_id,referrer_email_type_id, invitation_id,user_id,final_test_status,lender_offer_id, date_accessed,user_signed ;
drop Table finalTEDD1;
Here , signup_2 is not getting aggregated. Why is it so?