topic Nested if in QlikView

Nested if

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlik Experts I have a problem.

I'm trying to optimize an IF statement that I have in a Load. This is my IF expression:

If(Adt>0 and Adt <200,'E',

If(Adt>=200 and Adt <500,'D',

If(Adt>=500 and Adt <1200,'C',

If(Adt>=1200 and Adt <2700,'B',

If(Adt>=2700 and Adt <100000,'A'))))) as Segment

Any suggestion?

Thanks!!!

Re: Nested if

Anil_Babu_Samineni — Fri, 03 Mar 2017 03:54:51 GMT

I don't think whether we have another way to do this. Here, You can do class function but the data values you need as variant. So, Could be the best way as you wrote.

Re: Nested if

tresB — Fri, 03 Mar 2017 04:56:06 GMT

You can try using mapping load like:

Map:

Mapping Load

Low+IterNo()-1 as ABC,

SegmentMap

While IterNo()+Low<=High ;

load * Inline [

Low, High, SegmentMap

0, 200, E

200, 500, D

500, 1200, C

1200, 2700, B

2700, 100000, A

];

Load

ApplyMap('Map', Adt,null()) as Segment,

Adt;

Load

RecNo() as Adt

AutoGenerate 100000;

Re: Nested if

Fri, 03 Mar 2017 16:53:30 GMT

Actually there is nothing you can do about it.

So my main question is, why are you trying to optimize that?

You can always use mapping but I don't know if that is going to increase your performance, or whatever you are looking for.

Could you please be more specific about what you are trying to achieve?

Cheers, pal.

Re: Nested if

marioglasmv — Sat, 04 Mar 2017 06:05:28 GMT

Create the inline table like already mentioned here like :

load * Inline [

Low, High, SegmentMap

0, 200, E

200, 500, D

500, 1200, C

1200, 2700, B

2700, 100000, A

];

After that you have to use the INTERVALMATCH function. This function is there for situations like this.

Re: Nested if

maxgro — Sat, 04 Mar 2017 07:10:00 GMT

Another option, join

You got 4 different answers (if, applymap, interval match, join); if you can, try and let we know which is the one with the best performance in your case

load * Inline [

Low, High, SegmentMap

0, 200, E

200, 500, D

500, 1200, C

1200, 2700, B

2700, 100000, A

];

Load

Low+IterNo()-1 as Adt,

SegmentMap

Resident X

While IterNo()+Low<=High ;

DROP Table X;

Load floor(rand()*100000) as Adt AutoGenerate 100000;

Left Join (Z)

LOAD

Adt,

SegmentMap

Resident Y;

DROP Table Y;

Re: Nested if

MarcoWedel — Sat, 04 Mar 2017 19:19:04 GMT

another one:

If(Adt>0,

If(Adt< 200,'E',

If(Adt< 500,'D',

If(Adt< 1200,'C',

If(Adt< 2700,'B',

If(Adt<100000,'A')))))) as Segment;

hope this helps

regards

Marco

Re: Nested if

MarcoWedel — Sat, 04 Mar 2017 22:09:26 GMT

Yet another:

If(Adt>0,Pick(Match(-1,Adt<200,Adt<500,Adt<1200,Adt<2700,Adt<100000),'E','D','C','B','A')) as Segment

table1:
LOAD *,
    If(Adt>0,Pick(Match(-1,Adt<200,Adt<500,Adt<1200,Adt<2700,Adt<100000),'E','D','C','B','A')) as Segment;
LOAD Ceil(pow(10,1+4.3*Rand()))  as Adt
AutoGenerate 30;

regards

Marco

Re: Nested if

Peter_Cammaert — Sun, 05 Mar 2017 18:08:37 GMT

Again, an eye-opener, Marco ! Thanks

Re: Nested if

MarcoWedel — Sun, 05 Mar 2017 21:33:43 GMT

or maybe:

If(Adt>0,Mid('EDCBA',Match(-1,Adt<200,Adt<500,Adt<1200,Adt<2700,Adt<100000),1))

Marco

Re: Nested if

Peter_Cammaert — Sun, 05 Mar 2017 21:41:27 GMT

Any idea whether this one perfoms better/is faster than the typical nested IF from the Original Post?

Re: Nested if

MarcoWedel — Sun, 05 Mar 2017 21:50:24 GMT

No, I didn't test the performance, so using a string operation it might even be slower ...

Re: Nested if

Anonymous — Tue, 07 Mar 2017 23:33:46 GMT

Thanks a lot Marco!!

I tried all the different options and this was the option with the best performance.

Re: Nested if

MarcoWedel — Tue, 07 Mar 2017 23:50:42 GMT

Glad it helped

thanks for your feedback.

regards

Marco