Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Nested if

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!!!

1 Solution

Accepted Solutions
MarcoWedel

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

QlikCommunity_Thread_251976_Pic1.JPG

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

View solution in original post

13 Replies
Anil_Babu_Samineni

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tresesco
MVP
MVP

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;

Not applicable
Author

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.

marioglasmv
Partner - Contributor III
Partner - Contributor III

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.

maxgro
MVP
MVP

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

X:

load * Inline [

Low, High, SegmentMap

0, 200, E

200, 500, D

500, 1200, C

1200, 2700, B

2700, 100000, A

];

Y:

Load

  Low+IterNo()-1 as Adt,

    SegmentMap

Resident X

While IterNo()+Low<=High ;

DROP Table X;

Z:

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

Left Join (Z)

LOAD

  Adt,

  SegmentMap

Resident Y;

DROP Table Y;

MarcoWedel

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

MarcoWedel

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

QlikCommunity_Thread_251976_Pic1.JPG

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Again, an eye-opener, Marco ! Thanks

MarcoWedel

or maybe:

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

Marco