Pick and Match function

[ashis]

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,

[jonathandienst]

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;

[effinty2112]

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

[sunny_talwar · Accepted solution]

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

];

[Kushal_Chawda]

see this

If Condition?

[ashis]

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

[jonathandienst]

@sunny - clever code!

[sunny_talwar]

Thanks jontydkpi

[sunny_talwar]

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?

[ashis]

Yes , thank you.