5 Replies Latest reply: Sep 10, 2017 4:37 AM by Supriya R RSS

    issue while getting sum in script

    Supriya R

      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.


        • Re: issue while getting sum in script
          Vineeth Pujari

          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 ;




            • Re: issue while getting sum in script
              Supriya R

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

                • Re: issue while getting sum in script
                  Vineeth Pujari

                  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;

                    • Re: issue while getting sum in script
                      Supriya R

                      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?

                • Re: issue while getting sum in script
                  Anand Chouhan

                  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;