+ Start a Discussion
Ksenia ChoateKsenia Choate 

Case Statement in Einstein Analytics (Wave) SAQL

I edited SAQL to show month name instead of number in a dashboard step, using a case statement. The steps groups case records by Created Month and limits the data to cases created last and current month. The month name now shows up as expected (e.g., "December" instead of "12"), but the toggle using the step doesn’t facet properly. I am not a developer, and put together the case statement based on examples I found on the internet. I am clearly missing something, and I don’t understand every piece of code I have, so troubleshooting has been very much trial and error, but now I ran out of options.

Here is the code from an out-of the box step that shows month numbers and that works properly:
 
q = load "ServiceCase";
q = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in ["1 month ago".."current month"];
q = group q by 'CreatedDate_Month';
q = foreach q generate 'CreatedDate_Month' as 'CreatedDate_Month', count() as 'count';
q = order q by 'count' desc;
q = limit q 2000;

Here is my code that shows month names and doesn’t facet properly:
q = load "ServiceCase";
q = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in ["1 month ago".."current month"];
q = group q by 'CreatedDate_Month';
q = foreach q generate (case when 'CreatedDate_Month' in ["01"] then "January" when 'CreatedDate_Month' in ["02"] then "February" when 'CreatedDate_Month' in ["03"] then "March" when 'CreatedDate_Month' in ["04"] then "April" when 'CreatedDate_Month' in ["05"] then "May" when 'CreatedDate_Month' in ["06"] then "June" when 'CreatedDate_Month' in ["07"] then "July" when 'CreatedDate_Month' in ["08"] then "August" when 'CreatedDate_Month' in ["09"] then "September" when 'CreatedDate_Month' in ["10"] then "October" when 'CreatedDate_Month' in ["11"] then "November" when 'CreatedDate_Month' in ["12"] then "December" else "N/A" end) as 'CreatedDate_Month', count() as 'count';
q = order q by 'count' desc;
q = limit q 2000;

And by "it doesn't facet properly," I mean that when I click on the month name in the toggle, all widgets display the "No reults found" message. When I click on the same month name again, it does the same as the "Reset" feature, i.e., returts all widgets into their default position. When I click on the same month name the thrid time, I get the "No reults found" message again, etc.

Could someone please help me understand what I am doing wrong? Any help would be greatly appreciated. Thank you so very much in advance!
 
Best Answer chosen by Ksenia Choate
Ksenia ChoateKsenia Choate
Alain, thank you so much for your response, and I apologize for taking several days to get back to you! Did you by chance try using this in a dashboard widget to see if the dashboard facets properly when the step behind the widget uses this case statement? I couldn't get mine to facet (even though the SAQL query within the step returned no errors), and I still don't know why.

What was recommended to me and what I ended up doing is creating a new computeExpression node in the dataflow, creating a new field (I named it "Month") within the node, and usuing the case expression within that field. This is the syntax I used:
 
case CreatedDate_Month 
when "01" then "01 - January"
when "02" then "02 - February"
when "03" then "03 - March"
when "04" then "04 - April"
when "05" then "05 - May"
when "06" then "06 - June"
when "07" then "07 - July"
when "08" then "08 - August"
when "09" then "09 - September"
when "10" then "10 - October"
when "11" then "11 - November"
when "12" then "12 - December"
end

(I added numbers to the month names so that they are displayed in the correct order on dashboard widgets, otherwise they are ordered alphabetically). 

So that is what worked for me. 

Alain, thank you again for taking the time to look into this problem!
 

All Answers

Alain CabonAlain Cabon
Hi,

The result is "read only" indeed but have you got the results. Your conversion of months seems correct when I used it.
q = load "case";
q = group q by ('CreatedDate_Year', 'CreatedDate_Month');
q = foreach q generate 'CreatedDate_Year' as 'CreatedDate_Year', (case when 'CreatedDate_Month' in ["01"] then "January" when 'CreatedDate_Month' in ["02"] then "February" when 'CreatedDate_Month' in ["03"] then "March" when 'CreatedDate_Month' in ["04"] then "April" when 'CreatedDate_Month' in ["05"] then "May" when 'CreatedDate_Month' in ["06"] then "June" when 'CreatedDate_Month' in ["07"] then "July" when 'CreatedDate_Month' in ["08"] then "August" when 'CreatedDate_Month' in ["09"] then "September" when 'CreatedDate_Month' in ["10"] then "October" when 'CreatedDate_Month' in ["11"] then "November" when 'CreatedDate_Month' in ["12"] then "December" else "N/A" end) as 'CreatedDate_Month', count() as 'count';
q = order q by 'count' desc;
q = limit q 2000;

User-added image

Alain
Ksenia ChoateKsenia Choate
Alain, thank you so much for your response, and I apologize for taking several days to get back to you! Did you by chance try using this in a dashboard widget to see if the dashboard facets properly when the step behind the widget uses this case statement? I couldn't get mine to facet (even though the SAQL query within the step returned no errors), and I still don't know why.

What was recommended to me and what I ended up doing is creating a new computeExpression node in the dataflow, creating a new field (I named it "Month") within the node, and usuing the case expression within that field. This is the syntax I used:
 
case CreatedDate_Month 
when "01" then "01 - January"
when "02" then "02 - February"
when "03" then "03 - March"
when "04" then "04 - April"
when "05" then "05 - May"
when "06" then "06 - June"
when "07" then "07 - July"
when "08" then "08 - August"
when "09" then "09 - September"
when "10" then "10 - October"
when "11" then "11 - November"
when "12" then "12 - December"
end

(I added numbers to the month names so that they are displayed in the correct order on dashboard widgets, otherwise they are ordered alphabetically). 

So that is what worked for me. 

Alain, thank you again for taking the time to look into this problem!
 
This was selected as the best answer
Alain CabonAlain Cabon
Ok, so you have solved your problem now with your new computeExpression node in the dataflow ? or something is still not working ?

You could choose your answer as the best answer if your problem is solved (and post a link perhaps to another forum which explained the solution). 

Your first lens could be used in a dashboard with a date selector but your problem was also the order by month which was broken with the labels instead of the numerical values?
Ksenia ChoateKsenia Choate
Alain, you are correct - adding the new node to the dataflow solved my problem.

The order of months was a minor issue. I mentioned it in the explanation only because month names in the new case statement didn't match what I had in the original case statement, so I just wanted to clarify why.

This was my biggest problem: when I used the step/lens with the original case statement in a dashboard widget, and selected one of the months, I got the "No results found" error in the other widgets, as shown in the screenshot below. Here, I clicked "November" in the toggle widget that was built off a step with the original case statement and the data disappeared from the other widgets:

User-added image

With the new solution (the new Month field built within the new computeExpression node), when I do the same thing, I actually get correct results, as shown in the screenshot below. Here, I also selected November and the other widgets are now faceting properly:

User-added image



 
Alain CabonAlain Cabon
Ok, excellent so you have solved your problem completely by yourself (including the problem of faceting) and you can close your question with your own solution.  I am a beginner with Wave Analytics (only the trailhead modules and projects, I don't use Analytics at work) and I am learning above all here with "real world" problems at the same time as I try to help. Manipulating the JSON dataflow and the computeExpression Transformation are already intermediate or even advanced techniques (not used in the trails).

Have a nice week-end.

Alain
Ksenia ChoateKsenia Choate
Alain, I consider myself a beginner too. I took a three-day free class that Salesforce offered in my city, which was helpful. That said, I also get assistance from a Salesforce solution architect when I feel like I tried everything I could (he was the one who suggested creating the new node). In any case, I am grateful for your help and the time you put into this! Have a wonderul holiday season!
Rocco Di Conza 1Rocco Di Conza 1
Hi All,
I am using the standard calendar widget. I notice if we make a selection and switch to another Tabs in the same dashboard and we return back it preserve time selection, but not user indicator, wich indicates always "Year".
Does someone know this issue? There is a solution?
Many thanks
Michelle Yan 8Michelle Yan 8
This is the syntax I put into my Compute -Expression Node before the register node: 

case when 'Invoice__c.Invoice_Date__c_Month' in ["01"] then "1 - January" when 'Invoice__c.Invoice_Date__c_Month' in ["02"] then "2 - February" when 'Invoice__c.Invoice_Date__c_Month' in ["03"] then "3 - March" when 'Invoice__c.Invoice_Date__c_Month' in ["04"] then "4 - April" when 'Invoice__c.Invoice_Date__c_Month' in ["05"] then "5 - May" when 'Invoice__c.Invoice_Date__c_Month' in ["06"] then "6 - June" when 'Invoice__c.Invoice_Date__c_Month' in ["07"] then "7 - July" when 'Invoice__c.Invoice_Date__c_Month' in ["08"] then "8 - August" when 'Invoice__c.Invoice_Date__c_Month' in ["09"] then "9 - September" when 'Invoice__c.Invoice_Date__c_Month' in ["10"] then "10 - October" when 'Invoice__c.Invoice_Date__c_Month' in ["11"] then "11 - November" when 'Invoice__c.Invoice_Date__c_Month' in ["12"] then "12 - December" else "N/A" end
Arjun Patel 16Arjun Patel 16
Question on a case statement - im trying to use a case statement to display the value of an existing field.  See code below.  i tried it but failed.  Any guidance appreciated. thanks

Case 'Owner_Region__c'
when “Marketing Queue Lead” then 'Created_By_User_s_Region__c'
else 'Owner_Region__c'
end
 
Ben LeylandBen Leyland
hi Arjun, I'm experiencing a similar issue trying to return the value of an existing field with a case statement. Did you ever figure this one out? Thanks!