1

Is it possible to Order a SAQL query using a case statement? We changed CloseDate_Month in to text so it displays as month name instead of month number, but when that's used in the order by, it order by month name alphabetically. I couldn't find any syntax for case statements in an order by.

q = load "Opportunity_Pipeline_Data2";
q = filter q by 'StageName' in ["Closed Lost", "Stage 7"];
q = filter q by 'Account_Type__c' == "Prospect";
q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["2 quarters ago".."1 quarter ahead"];
q = group q by ('CloseDate_Month', 'StageName');
q = foreach q generate case when ('CloseDate_Month' in ["01"]) then "Jan" when ('CloseDate_Month' in ["02"]) then "Feb" when ('CloseDate_Month' in ["03"]) then "Mar" when ('CloseDate_Month' in ["04"]) then "Apr" when ('CloseDate_Month' in ["05"]) then "May" when ('CloseDate_Month' in ["06"]) then "Jun" when ('CloseDate_Month' in ["07"]) then "Jul" when ('CloseDate_Month' in ["08"]) then "Aug" when ('CloseDate_Month' in ["09"]) then "Sep" when ('CloseDate_Month' in ["10"]) then "Oct" when ('CloseDate_Month' in ["11"]) then "Nov" when ('CloseDate_Month' in ["12"]) then "Dec" else "N/A" end as 'CloseDate_Month', 'StageName' as 'StageName', sum('Amount') as 'sum_Amount';
q = order q by ((case CloseDate_Month
when "Jan" then 01
when "Feb" then 02
when "Mar" then 03
when "Apr" then 04
when "May" then 05
when "Jun" then 06
when "Jul" then 07
when "Aug" then 08
when "Sep" then 09
when "Oct" then 10
when "Nov" then 11
when "Dec" then 12
 end asc), 'StageName' asc);
q = limit q 2000
1
  • By the way, you can avoid the case() call in the foreach statement by using the string_to_date() function and using the string format "MMM". – Vance Lopez Apr 19 '18 at 22:53
1

You can have that in select clause and use the new field in order by clause something like this:-

q = load "Opportunity_Pipeline_Data2";
q = filter q by 'StageName' in ["Closed Lost", "Stage 7"];
q = filter q by 'Account_Type__c' == "Prospect";
q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["2 quarters ago".."1 quarter ahead"];
q = group q by ('CloseDate_Month', 'StageName');
q = foreach q generate case when ('CloseDate_Month' in ["01"]) then "Jan" when ('CloseDate_Month' in ["02"]) then "Feb" when ('CloseDate_Month' in ["03"]) then "Mar" when ('CloseDate_Month' in ["04"]) then "Apr" when ('CloseDate_Month' in ["05"]) then "May" when ('CloseDate_Month' in ["06"]) then "Jun" when ('CloseDate_Month' in ["07"]) then "Jul" when ('CloseDate_Month' in ["08"]) then "Aug" when ('CloseDate_Month' in ["09"]) then "Sep" when ('CloseDate_Month' in ["10"]) then "Oct" when ('CloseDate_Month' in ["11"]) then "Nov" when ('CloseDate_Month' in ["12"]) then "Dec" else "N/A" end as 'CloseDate_Month', 'StageName' as 'StageName', sum('Amount') as 'sum_Amount';
q = foreach q generate case when (write case stetment here end as 'CloseDate_Month2';
q = order q , 'CloseDate_Month2','StageName' asc);
q = limit q 2000

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.