1

I am trying to use SAQL queries in Apex and trying to execute following logic in my developer console. It gives me uncommitted work pending error which we basically get during a DML before a callout. This is the SAQL code given my analytics team and I am supposed to execute the same thing in apex. How to perform multiple SAQL queries in the same transaction and finally cogroup query,query2 to get the output is what I am trying to figure out. How to go about this solution.

String query = 'q = load "0Fb19000000BFX1CAO/0Fc19000000OwqcCAC";q = filter q by call_direction == "outbound";q = filter q by call_date_Week not in ["53","52"];';
  query = query+'q = filter q by date(call_date_Year, call_date_Month, call_date_Day) in [dateRange([2018,11,1],[2020,1,31])];';
  query = query+'q = group q by (call_date_Year, call_date_Month, call_date_Day);q = foreach q generate call_date_Year, call_date_Month, call_date_Day, sum(total_mou) as mou;';
  query = query+'q = fill q by (dateCols=(call_date_Year, call_date_Month, call_date_Day, "Y-M-D"));';
  query = query+'q = timeseries q generate mou as mou_proj with (length=7, predictionInterval=95, dateCols=(call_date_Year, call_date_Month, call_date_Day, "Y-M-D"));';
  query = query+'q = foreach q generate call_date_Year + "~~~" + call_date_Month + "~~~" + call_date_Day as cldmy, call_date_Year, call_date_Month,call_date_Day,mou,mou_proj,(mou - mou_proj) as variance,case when mou_proj_low_95 <= 0 then 0 else mou_proj_low_95 end as mouprojlow_95,mou_proj_high_95';
  ConnectApi.LiteralJson queryresult =  ConnectApi.Wave.executeQuery(query);
  String response = queryresult.json;
  system.debug('at 4 '+response);

  string query2 = 'r = load "0Fb19000000BFX1CAO/0Fc19000000OwqcCAC";';
  query2 = query2+'filter r by call_direction == "outbound";';
  query2 = query2+'filter r by date(call_date_Year, call_date_Month, call_date_Day) in [dateRange([2018,11,1],[2020,12,31])];';
  query2 = query2+'group r by (call_date_Year, call_date_Month, call_date_Day,"voice.account.Global_Account_Number_Displayed__c");';
  query2 = query2+'r = foreach r generate call_date_Year + "~~~" + call_date_Month + "~~~" + call_date_Day as cldmy, call_date_Year,call_date_Month,call_date_Day,sum(total_mou) as mou;';
  query2 = query2+'r = fill r by (dateCols=(call_date_Year, call_date_Month, call_date_Day, "Y-M-D"));';
  ConnectApi.LiteralJson queryresult1 =  ConnectApi.Wave.executeQuery(query2);
  String response1 = queryresult1.json;
  system.debug('at 19 '+response1);

  string query3= 'out = cogroup query2 by ("call_date_Year", "call_date_Month", "call_date_Day") left,query("call_date_Year", "call_date_Month", "call_date_Day");';
  query3 = query3+'out = foreach out generate qcall_date_Year + "~~~" + query2.call_date_Month + "~~~" + query2.call_date_Day as cldmy, coalesce(sum(query2["mou"]),sum(query[mou])) as minutes, sum(query["mou_proj"]) as projection, sum(query["mou_proj_low_95"]) as projection_low, sum(query["mou_proj_high_95"]) as projection_high,case when sum(query2["mou"]) > sum(q[mou_proj_high_95]) then 1 when sum(r["mou"]) < sum(query[mou_proj_low_95]) then 1 else 0 end as out_of_bounds;';
  query3 = query3+'out = filter out by date_diff("day", toDate("cldmy","yyyy~~~MM~~~dd"), now()) <= 90;';                                                                                                 
  ConnectApi.LiteralJson queryresult1 =  ConnectApi.Wave.executeQuery(query3);
  • Which of the ConnectApi.Wave.executeQuery calls generates the error? And are you absolutely sure there is no DML before these calls? – Keith C Mar 21 at 8:35
0

I have never called SAQL through Apex... However what jumps to my eye is the missing/wrong stream identifier in query3

out = foreach out generate qcall_date_Year [...]

My guess is, that instead of qcall_date_Year you mean query2.call_date_Year like the following date parts in that query.

Hope that helps already.

| improve this answer | |

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.