Nest If Statement

[#1]

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

[Josh_Good · Accepted solution]

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;