Analytics SDK Developer Guide
j
No Results
Search Tips:
- Please consider misspellings
- Try different search keywords
CRM Analytics Apex QueryBuilder Examples
Build simple or complex SAQL queries using QueryBuilder.
QueryBuilder is the core of the CRM Analytics Apex feature set, so let’s take a closer look.
1. Here’s a simple count query:
Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.count().alias('c')};
String query = Wave.QueryBuilder.load('datasetId', 'datasetVersionId').group().foreach(projs).build('q');Output:
q = load "datasetId/datasetVersionId";
q = group q by all;
q = foreach q generate count as c;2. Query selecting specific attributes and using aliases.
Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
String query = Wave.QueryBuilder.load('datasetId','datasetVersionId').foreach(projs).build('q');Output:
q = load "datasetId/datasetVersionId";
q = foreach q generate Name,AnnualRevenue as Revenue;3. Query using a filter condition.
Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
String query = Wave.QueryBuilder.load('datasetId','datasetVersionId').foreach(projs).filter('Name != \'My Name\'').build('q');Output:
q = load "datasetId/datasetVersionId";
q = foreach q generate Name,AnnualRevenue as Revenue;
q = filter q by Name != 'My Name';4. Query with a limit statement.
Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
String query = Wave.QueryBuilder.load('datasetId','datasetVersionId').foreach(projs).cap(10).build('q');Output:
q = load "datasetId/datasetVersionId";
q = foreach q generate Name,AnnualRevenue as Revenue;
q = limit q 10;5. Query with an order statement.
Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
List<List<String>> orders = new List<List<String>>{new List<String>{'Name', 'asc'}, new List<String>{'Revenue', 'desc'}};
String query = Wave.QueryBuilder.load('datasetId','datasetVersionId').foreach(projs).order(orders).cap(10).build('q');Output:
q = load "datasetId/datasetVersionId";
q = foreach q generate Name,AnnualRevenue as Revenue;
q = order q by (Name asc, Revenue desc);
q = limit q 10;6. Query with a union statement.
Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
Wave.QueryNode nodeOne = Wave.QueryBuilder.load('dataseOne','datasetVersionOne').foreach(projs);
Wave.QueryNode nodeTwo = Wave.QueryBuilder.load('datasetTwo', 'datasetVersionTwo').foreach(projs);
String query = Wave.QueryBuilder.union(new List<Wave.QueryNode>{nodeOne, nodeTwo}).build('q');Output:
qa = load "datasetOne/datasetVersionOne";
qa = foreach q generate Name,AnnualRevenue as Revenue;
qb = load "datasetTwo/datasetVersionTwo";
qb = foreach q generate Name,AnnualRevenue as Revenue;
q = union qa, qb;7. Executing the query to get the result set via Query Builder.
Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.count().alias('c')};
ConnectApi.LiteralJson result = Wave.QueryBuilder.load('datasetId', 'datasetVersionId').group().foreach(projs).execute('q');8. Example of grouping by a specific dataset attribute.
Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('Revenue').sum().alias('REVENUE_SUM')};
ConnectApi.LiteralJson result = Wave.QueryBuilder.load('datasetId', 'datasetVersionId').group(new String[]{'Name'}).foreach(projs).build('q');Output:
q = load "datasetId/datasetVersionId";
q = group q by (Name);
q = foreach q generate Name,sum(Revenue) as REVENUE_SUM;