Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
berryandcherry6
Not applicable

aggregate count for particular field in table

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.

1 Reply
jontydkpi
Not applicable

Re: aggregate count for particular field in table

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein