Nest If Statement

Fri, 20 Dec 2013 16:10:33 GMT

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 — Fri, 20 Dec 2013 17:23:22 GMT

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;

topic Nest If Statement in QlikView

Nest If Statement

Re: Nest If Statement