issue while getting sum in script

[berryandcherry6]

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.

[vinieme12]

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 ;

[its_anandrjs]

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;

[berryandcherry6]

No, even after preceding mapping table with load result remains same.

[vinieme12]

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;

[berryandcherry6]

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?