1 Reply Latest reply: Dec 20, 2013 12:23 PM by Josh Good RSS

    Nest If Statement

    David Young

      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?



        • Re: Nest If Statement
          Josh Good

          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.









          From Table;




          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;