Bucket field in CRM Analytic

There are a few options to bucket field in CRM Analytics; let us see each option:

Scenario: field name = Option, bucket value A, B, C as "Executive", D, E, F as "Premier", all others as "Standard".

1. SAQL in the dashboard

Edit the widget and hit query mode

q = load "SalesData1";
q = filter q by 'Country' == "Singapore";
q = filter q by 'Option' is not null;
q = foreach q generate (case when Option in ["A","B","C"] then "Executive" when Option in ["D","E","F"] then "Premier" else "Standard" end) as 'Bucket';
q1 = group q by 'Bucket';
q1 = foreach q1 generate 'Bucket', count(q1) as 'Count';

This will work; however, there will be issues:

• Unable to broadcast selection as facet from the chart with SAQL because the identifier is unknown. When selecting the grouping, other widgets will get an error "Can't display this widget. This widget can't be displayed because there is a problem with its source query: Undefined identifier: "SAQL Field As". Make sure the "SAQL Field As" identifier exists and is spelled correctly without spaces.". However, the widget will still be able to receive filters from other widgets. You can deactivate "Broadcast selections as facets" for that query, but it is not ideal.
• Performance factors, system recommended: No Groupings After Projections and No Case Statements in Projections

Another sample:

q = load "SalesData1";
q = filter q by 'Country' == "Singapore";
q = filter q by 'Option' is not null;
q = group q by ('StartTime', 'Owner.Region__c');
q = foreach q generate q.'StartTime' as 'StartTime', (case when 'Owner.Region__c' is null then "Unknown" else 'Owner.Region__c' end) as 'Region', count(q) as 'Count';
q = order q by ('StartTime' asc, 'Region' asc);
q = limit q 2000;

2. Edit field value

Issue:

• Loss of the original field value
• The fields will not be grouped, even if they have the same value

From the screenshot above, the three Executive originally were A, B, and C; they are not combined by the system.

3. Using Recipe or Dataflow to create new fields

This is the most ideal option, but you have to edit the recipe or data.