SAQL to generate a field not in a group by statement

Question

I'm a bit of a beginner to SAQL. I have been tasked with maintaining a fairly elaborate dashboard and data flow and have been asked to add some features to it. There is a lens that displays a chart in which I need to pull another field in order to display it in a tooltip. However, the SAQL side of it includes a group by statement and I'm running into some issues adding the new field to the generate statement.

q = load "main_data_set";
q = group q by ('Value_Name__c', 'TeamMemberName');
q = foreach q generate 'Value_Name__c' as 'Value_Name__c', 
'TeamMemberName' as 'TeamMemberName', sum('Value__c') as 'sum_Value__c', 
sum('Value_Index__c') as 'sum_Value_Index__c', 'NEW_FIELD__c' as 
'NEW_FIELD__c'; 
q = order q by 'sum_Value_Index__c' asc;
q = limit q 20000;

When I added NEW_FIELD__c to the generate statement, I get the following error when trying to run the query:

Non grouped dimension in a grouped query: 'Annotation__c'

It runs fine when I add NEW_FIELD__c to the group by statement, but I don't want the data grouped by this field, I just want to be able to access it in the graph configuration. What am I doing wrong?

Answer 1

If you are able to group by the field with out changing the result of your query I would suggest using a saql step type. This will allow you to display certain fields in the chart and use the hidden fields in the tooltip. Below is an example of the JSON. This can also be done in the UI by setting the field to be unused.

"STEP_NAME": {
                "type": "saql",
                "query": "q = load "main_data_set";\nq = group q by ('Value_Name__c', 'TeamMemberName','NEW_FIELD__c');\nq = foreach q generate 'Value_Name__c', 'TeamMemberName', sum('Value__c') as 'sum_Value__c', sum('Value_Index__c') as 'sum_Value_Index__c', 'NEW_FIELD__c';\nq = order q by 'sum_Value_Index__c' asc;\nq = limit q 20000;",
                "useGlobal": true,
                "numbers": [
                    "sum_Value__c",
                    "sum_Value_Index__c"
                ],
                "groups": [
                    "Value_Name__c",
                    "TeamMemberName"
                ],
                "strings": [
                    "Value_Name__c",
                    "TeamMemberName",
                    "NEW_FIELD__c"
                ],
                "label": "STEP_NAME",
                "selectMode": "single",
                "broadcastFacet": true,
                "receiveFacet": true
            },