function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nevin O'Regan 3Nevin O'Regan 3 

Complex SAQL Formula

Hi guys,

I have created two lenses which calculate the No. of rows after I apply a couple of filters to the dataset. One gives me the Full Allocation and the other gives me Partial Allocation. 

Partial
q = load "FillRatio201902";
q = filter q by 'BO_Qty' > 0;
q = filter q by 'Status' != "Cancelled";
q = filter q by 'Formula' < 0;
q = group q by all;
q = foreach q generate count() as 'count';
q = limit q 2000;

Full Allocation
q = load "FillRatio201902";
q = filter q by 'BO_Qty' == 0;
q = filter q by 'Status' != "Cancelled";
q = filter q by date('Allocated_Date_Year', 'Allocated_Date_Month', 'Allocated_Date_Day') in [dateRange([1970,10,6], [2019,3,3])];
result = group q by all;
result = foreach result generate count(q) as 'A', sum(q.'BO_Qty') as 'B';
result = limit result 2000;

I now need to calculate a 'Fill Ratio' which includes the results of the Full Allocation and Partial Allocation in it.
Fill Ratio = Full Allocation + (Partial * 0.5) / Total No. Of Rows

Is it even possible to calculate this through SAQL?