Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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.
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;
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.
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.
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;
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
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
Again, an eye-opener, Marco ! Thanks
or maybe:
If(Adt>0,Mid('EDCBA',Match(-1,Adt<200,Adt<500,Adt<1200,Adt<2700,Adt<100000),1))
Marco