1 Reply Latest reply: Sep 19, 2017 7:01 AM by Jonathan Dienst RSS

    aggregate count for particular field in table

    Supriya R

      Hi,

       

      i have a table  FactTableDetail , here i need to aggregate  count of LandingHits and signedUser for ab_group_id in script . I built below code but it aggregate to campaign_id.

       

       

      FactTable:

      Load

      `invitation_id`,

          `client_id`,

          `batch_meta_data_id`,

          `campaign_id`,

          `user_signed`,

      `final_test_status`,

          `course_completed`,

      `log_type_id`,

          date_accessed,

      `batch_name`,

      `campaign_name`,

      `client_name`,

          `ab_group_id`

          Resident INVITATION ;

      drop Table INVITATION;

       

       

      FactTableDetail:

      Load

      `client_id`,

          `batch_meta_data_id`,

          `campaign_id`,

      `batch_name`,

      `campaign_name`,

      `client_name`,

          `ab_group_id`,

           Count(distinct if(LEN(date_accessed)>0 , [invitation_id])) as LandingHits,

          Count(DISTINCT IF([user_signed]='1',[invitation_id])) as signedUser,

          Resident FactTable group by client_id, batch_meta_data_id, campaign_id, ab_group_id, `batch_name`,`campaign_name`,

      `client_name`;

      drop Table FactTable;

       

      How could i do this? Please help me on this.

        • Re: aggregate count for particular field in table
          Jonathan Dienst

          This is one way:

           

          FactTable:

          Load

            invitation_id,

            client_id,

            batch_meta_data_id,

            campaign_id,

            user_signed,

            final_test_status,

            course_completed,

            log_type_id,

            date_accessed,

            batch_name,

            campaign_name,

            client_name,

            ab_group_id

          Resident INVITATION ;

          DROP Table INVITATION;

           

          Join(FactTable):

          Load

            ab_group_id,

            Count(DISTINCT If(LEN(date_accessed) > 0, invitation_id)) as LandingHits,

            Count(DISTINCT If(user_signed= '1', invitation_id)) as signedUser,

          Resident FactTable

          Group By ab_group_id;