4 Replies Latest reply: Sep 6, 2017 8:29 AM by omar bensalem RSS

    SUM data in Load script and keep in FactTable

    Supriya R

      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