Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;