Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to remove nested if condition with pick and match , and try to create bucket . My if conditions are working fine.
I am trying the same with pick and match function , could you please help me what would be the correct syntax for it.
Student:
LOAD * INLINE [
Name, Subjects, Marks
XXX, Stat, 15
YYY, Stat, 25
ZZZ, Stat, 30 ]
Load *,
//(If(Marks <=20, '< 20',
//If(Marks >20 and Marks <=40, '< 40',
//If(Marks >40 and Marks <=90, '< 90',
//If(Marks >90, '90+',Marks))))) as MarksBuckets,
pick(Match(Marks,'<=20','>20 and <=40','40 and <=90','>90'),'< 20','< 40','< 90','90+') as MarksBuckets,
resident Student;
Thank you,
You can also try this
Student:
LOAD *,
Pick(Match(-1, Marks <= 20, Marks > 20 and Marks <= 40, Marks > 40 and Marks <= 90, Marks > 90), '<=20','>20 and <=40','40 and <=90','>90') as MarksBuckets;
LOAD * INLINE [
Name, Subjects, Marks
XXX, Stat, 15
YYY, Stat, 25
ZZZ, Stat, 30
];
Match is used to match a text string and cannot be used to evaluate a comparison that way. If you want to bucket in the front end, you can use Class() for regular buckets, or a nested If for irregular buckets. Or else do this in the Load script using the same nested If, or use an Interval Match.
You can simplify your If by removing the redundant conditions:
Load *,
(If(Marks <= 20, '< 20',
If(Marks <= 40, '< 40',
If(Marks <= 90, '< 90', '90+',Marks)))
) as MarksBuckets,
Resident Student;
Hi Ashis,
Sorry to say you can't use match the way you want to use it. I've often wished there was a function that could match according to a logical list of inequalities.
You could consider using the Interval Match function:
Student:
LOAD * INLINE [
Name, Subjects, Marks
XXX, Stat, 15
YYY, Stat, 25
ZZZ, Stat, 30 ];
Bins:
LOAD * INLINE [
From, To, Bin
0, 20, <=20
21, 40, <=40
41, 90, <=90
91, 100, 90+
];
IntervalMatch:
IntervalMatch(Marks) LOAD From, To Resident Bins;
Gives:
Name | Subjects | Marks | Bin |
---|---|---|---|
XXX | Stat | 15 | <=20 |
YYY | Stat | 25 | <=40 |
ZZZ | Stat | 30 | <=40 |
Good luck
Andrew
You can also try this
Student:
LOAD *,
Pick(Match(-1, Marks <= 20, Marks > 20 and Marks <= 40, Marks > 40 and Marks <= 90, Marks > 90), '<=20','>20 and <=40','40 and <=90','>90') as MarksBuckets;
LOAD * INLINE [
Name, Subjects, Marks
XXX, Stat, 15
YYY, Stat, 25
ZZZ, Stat, 30
];
see this
Thank you Sunny for your reply.
The above code worked . Could you please tell me why you used -1 after pick and match.
Thank you,
Ashis
@sunny - clever code!
Thanks jontydkpi
Each of the block will act as a if statement leading to -1 or 0. At one given time only one of them will be -1 and every where else there will be a 0. So the one with -1's position will determine which range is assigned to it.
Does that make sense?
Yes , thank you.