
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Pick and Match function
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,
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
see this

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@sunny - clever code!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks jontydkpi

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes , thank you.
