Skip to main content
小组* CRM Analytics (fka Tableau CRM) *Arjun Khanna 的帖子
Arjun Khanna (Birlasoft) 发布于 * CRM Analytics (fka Tableau CRM) *
2020年10月21日 13:24

Hi all, 

 

Have a use case that I need help with.

I have two datasets,

1. Contacts with Reservations

2. Contacts with Email results

 

The only field linking these 2 datasets is the contact Id.

I want to be able to select the Email name ( from second dataset ) in form of a global filter that would give me unique contacts for that 'email name' as a result.

 

Now for those contacts I need to find sum of total Reservations (which is doable), but grouped by Property Codes. (The first dataset has property info as well on reservations)

 

Eg. I select Email name as 'Invitation email' and I get 1000 contacts as a result.

For those 1000 contacts I need sum of reservations, grouped by Property Code.

 

Things that I have tried,

1. using cogroup in SAQL and grouping by contact Id , inner join. I get the sum of reservations but cant find a way to group the results by Property Code (as results are already grouped by Contact Id).

2. Using binding to send result of contact Ids from dataset 2 in a lens of dataset 1 . That is filter dataset 1 by contact Id in [result from binding]. Since there are hundreds of thousands contacts, I get a filter depth exceeded error.

3. Connect data sources doesn't seem to help out here as well.

 

Any kind of help here would be much appreciated.

4 条评论
  1. Arjun Khanna (Birlasoft)
    2020年10月26日 20:35

    got the solution to my use case by using the below query,

    thanks to

    @Roman Michalik

    for providing the solution,

    In my use case I have have cases and opportunities, both related to Accounts. Now I want to filter the list of accounts by case origin (or any other field in cases) and apply the remaining list of accounts as a filter for opportunities and display only those accounts filtered by cases which have an opportunity.

    This query is working for me:

    q1 = load "case";

    q1 = group q1 by '

    Account.Name

    ';

    q1 = foreach q1 generate '

    Account.Name' as 'Account.Name

    ', unique('Id') as 'unique_Id', 1 as 'relevant', "AAA" as 'StageName';

    q2 = load "opportunity";

    q2 = group q2 by ('

    Account.Name

    ', 'StageName');

    q2 = foreach q2 generate '

    Account.Name' as 'Account.Name

    ', 'StageName' as 'StageName', sum('Amount') as 'sum_Amount';

    q = cogroup q1 by ('

    Account.Name','StageName') full,q2 by ('Account.Name

    ','StageName');

    q = foreach q generate coalesce(q1.'

    Account.Name',q2.'Account.Name') as 'Account.Name

    ', coalesce(q1.'StageName',q2.'StageName') as 'StageName', coalesce(sum(q2.'sum_Amount'),0) as 'sum_Amount', coalesce(avg(q1.'relevant'),0) as 'relevant';

    --distribute "relevant" to all rows per Account

    q = group q by ('

    Account.Name

    ','StageName');

    q = foreach q generate '

    Account.Name', 'StageName', sum('sum_Amount') as 'sum_Amount',max(avg('relevant')) over([.. 0] partition by 'Account.Name

    ' order by 'StageName') as 'relevant';

    --filter only relevant Accounts

    q = filter q by 'relevant' > 0;

    --get rid of dummy data

    q = filter q by 'StageName' != "AAA";

撰写评论...
0/9000