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?

       

      David

        • 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.

           

          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;