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
Improve this question
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
    Commented Apr 19, 2018 at 22:53

1 Answer 1

Reset to default
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
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .