Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
gerardolalo4
New Contributor II

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
Highlighted

Re: Nested if

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
Highlighted

Re: Nested if

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.

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)
Highlighted
MVP
MVP

Re: Nested if

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;

Highlighted
Not applicable

Re: Nested if

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.

Partner
Partner

Re: Nested if

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.

Highlighted
MVP
MVP

Re: Nested if

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;

Highlighted

Re: Nested if

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

Highlighted

Re: Nested if

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

Highlighted

Re: Nested if

Again, an eye-opener, Marco ! Thanks

Highlighted

Re: Nested if

or maybe:

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

Marco