I need your help with the fill() SAQL function. I have created the query below and it runs, but the fill() doesn't work (no zeros get inserted). Please let me know what I am doing wrong here. Thank you for your help. I have tried to remove the windowing function and also to remove the partition but it still doesn't work.

I would like the windowing function to look back at 12 calendar months, regardless of whether they had any value or not.

I worked based on a sample/explanation here.

q = load "OLIs1";

q = group q by ('Account.Area__c', 'ProductClass__c', 'Order.BookingDate__c_Year', 'Order.BookingDate__c_Month');

q = foreach q generate 'Account.Area__c' as 'Account.Area__c', 'ProductClass__c' as 'ProductClass__c', 'Order.BookingDate__c_Year' as 'Year', 'Order.BookingDate__c_Month' as 'Month', sum('Quantity2__c') as 'Sum Qty', sum(sum('Quantity2__c')) over ([-12..-12] partition by ('Account.Area__c','ProductClass__c') order by ('Account.Area__c','ProductClass__c', 'Order.BookingDate__c_Year', 'Order.BookingDate__c_Month')) as 'Window';

q = fill q by (dateCols = ('Year', 'Month', "Y-M"), partition = 'ProductClass__c');

q = foreach q generate 'Account.Area__c' as 'Area', 'ProductClass__c' as 'Product Class', 'Year' as 'Year', 'Month' as 'Month', 'Sum Qty' as 'Sum Qty', 'Window' as 'Window';

q = order q by ('Area' asc, 'Product Class' asc, 'Year' asc, 'Month' asc);
q = limit q 2000;

Thank you and kind regards, Peter

  • The Salesforce documentation (of course) uses a slightly different syntax for the fill arguments though it appears to me that quotes should still be acceptable. Does removing the single quotes around Year and Month make a difference? ex. dateCols=(Year,Month,"Y-M"). Additionally, that second generate could be an issue. Why have two?
    – nbrown
    Nov 23 '20 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.