Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Our current process is we have a chart with the following expression in it?
=if(Aggr(count(WOID),AcctNum)= 0,0,
aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c < .1,0,0),AcctNum)+
aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .1,1,0),AcctNum)
+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .3,1,0),AcctNum)
+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .4,1,0),AcctNum)
+aggr(IF(count({<StatusDesc = {"Complete", "Closed*"}>}WOID)/Students__c >= .6,1,0),AcctNum)
+aggr(IF(sum({<StatusDesc = {"Complete", "Closed*"}>}ActualHours)/count({<StatusDesc = {"Complete", "Closed*"}>}WOID) >= .5,1,0),AcctNum))
What I want to do is move it to the Load Statement. What I am trying to accomplish is once I count the WOID for each account, I look at the Status of each of the WOID's and if they are complete and closed I divide them by the Number of Students. If the results is less that .1 they get a score of 0. I continue that same process with each account giving them a score up to 4. If they track hours on work orders I divide that by the number of WOID's and if the result is .5 of higher they get a point. So for each account they get a score of 0 to 4 and if they do the Actual Hours they can earn a point as well. Can this be accomplished in the Load Statement?
David
I think this will work. Note I assumed you had all the fields in the same table (I call it "BaseData"). if you don't you will need to do that in advance of my script.
BaseData:
Load
Students__c,
AcctNum,
WOID,
StatusDesc,
ActualHours
From Table;
Load
AcctNum,
If(Floor(Count (WOID) / Student__c *10) > 4, 4, Floor(Count (WOID) / Student__c *10))
+ if(Sum(ActualHours)/count(WOID) >0.5,1,0) as Score
Resident BaseData
Where StatusDesc = 'Complete' or StatusDesc like 'Closed*'
Group by AcctNum;
I think this will work. Note I assumed you had all the fields in the same table (I call it "BaseData"). if you don't you will need to do that in advance of my script.
BaseData:
Load
Students__c,
AcctNum,
WOID,
StatusDesc,
ActualHours
From Table;
Load
AcctNum,
If(Floor(Count (WOID) / Student__c *10) > 4, 4, Floor(Count (WOID) / Student__c *10))
+ if(Sum(ActualHours)/count(WOID) >0.5,1,0) as Score
Resident BaseData
Where StatusDesc = 'Complete' or StatusDesc like 'Closed*'
Group by AcctNum;