0

I need to query all activities for each account that I have an activity on.

I am using the activities dataset from the activity capture.

In SQL it would be

SELECT activityid, account.name, account.id FROM activity WHERE account.id IN [SELECT account.id, account.owner FROM activity WHERE account.owner = 'me']

Having a hard time trying to do the in SAQL

This is for a value table on a lens.

Is there a way to make a query and use that to filter another query?

Or use a query as a subset or array?

  • SAQL doesn't support subqueries in that way but there are usually alternatives. In what context are you running the SAQL? Is it part of a dashboard/lens? A Dataflow? Something else? I think your options are going to depend on that. – John Towers Mar 6 at 19:24
0

SAQL does not support subqueries but since you're wanting to do this in a lens, you have a few ways to accomplish what you want.

Solution 1 (Recipes):

First (and what I recommend) is using one or more recipes to create a dataset with the data you want all in one place. In your case it looks like you want to join two objects - Activity and Account - and then be able to filter on the Account Owner. There are step by step details on how to create a recipe in the documentation but an overview would look something like:

  1. Find the Activity dataset in the Data Manager and use it to start a recipe
  2. Click on the Add Data button in the recipe manager interface
  3. Choose the Account dataset
  4. Click on the Inner Join option to only include rows that exist in both result sets
  5. Choose the Activity and Account field(s) to join on
  6. Choose the Account Owner field to add to the dataset

The resulting set of data will be only rows of Activities that are related to Accounts. You can use this recipe to create a dataset and when you build your lens, you can use the resulting dataset. Then in your SAQL query you can filter on the Account Owner field and get the results you want. The upside here is that you don't need to modify the underlying SAQL in the lens to get the data you want. Since the Owner field is now part of the dataset you can use the UI to add the filter.

Solution 2 (SAQL):

If your use case requires you to do everything in SAQL then you can use a cogroup to achieve this. The cogroup statement lets you take two sets of data and combine them on some key. In an inner cogroup only values that exist in both sets will be in the output.

In your case then, you could do something like:

accounts = load 'Accounts';
accounts = filter accounts by 'Owner' == "me";
activities = load 'Activities';
result = cogroup accounts by 'Id', activities by 'AccountId';
result = foreach result generate accounts.'Id' as 'Account Id';

This would give you a filtered list of Accounts that you then join to Activities to produce a result that contains only Activities on the matching Accounts.

The above SAQL is just an example - you would have to modify it to reference the actual datasets and fields you care about. There are also more details and examples on cogrouping in the SAQL reference.

| improve this answer | |
  • It seems there is no way to cogroup the result there. I have the ids for the accounts but with no way to cogroup again I cant seem to do anything. – SomeSalesforceDev Mar 10 at 12:58
  • Made a recipe to get the first result table then cogrouped that and got what i wanted thanks. – SomeSalesforceDev Mar 10 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.