topic Re: Substitute to nested ifs in QlikView

Substitute to nested ifs

Thu, 21 Aug 2014 14:00:39 GMT

Hi Everyone

I've been trying to figure this one out for a while and am having no luck. I wish to get rid of my nested if statements in my variables as I have a lot of these

if(IsNull(sum({<Year,Month,Day, F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase))

(sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=3,50,

if((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >3

and (sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) <=4,45,

If((sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100) >20,25

))))))))))

Re: Substitute to nested ifs

giakoum — Thu, 21 Aug 2014 14:07:42 GMT

This is a really complicated statement. Depending on records, the performance must be really bad. I would suggest that you create flags in the data model (reload script) and use those in your set analysis statements.

Re: Substitute to nested ifs

marcus_sommer — Thu, 21 Aug 2014 14:55:01 GMT

I suggest you to use a pick(match() approach to simplify your expression. However pick(match()) works only with equal values - a greater or less didn't work. Therefore you need a logic to modify your condition in certain intervals or simply as listing. For your case a round-function like ceil() or floor() and a return-listing should be enough:

pick(match(

ceil(

sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100)),

1,2,3,4,5,6,7,8,9,10 .......),

50,50,50,45,45 .....)

With some adjustment this will work - I use it quite often.

- Marcus

Re: Substitute to nested ifs

Thu, 21 Aug 2014 15:40:00 GMT

This seems to be a big expression and not good for Performance. As a step towards performance tuning. You should aggregate most of stuff in Script.-Ram

Re: Substitute to nested ifs

jagan — Thu, 21 Aug 2014 16:05:28 GMT

Hi,

If you are using in a straight table then try like this, create two expressions

Exp1:

=(sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} SalesLossValue)/sum({<Year,Month,Day,F_Class_Code = {'1'},DATE={'>=$(vLast11Month)<=$(vEndDate)'}>} OrderCase)*100)

Exp2:

Now in your actual expression try like this

If([Exp1] > 3 AND [Exp2] <5, 45,

and in presentation tab hide Exp1 and Exp2, by this way you can reuse and reduce the size of the expression.

Hope this helps you.

Regards,

Jagan.

Re: Substitute to nested ifs

Fri, 22 Aug 2014 04:26:49 GMT

I am going to try these suggestions and let you guys know. Thanks a lot!