topic Re: Pick and Match function in QlikView

Pick and Match function

ashis — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Pick and Match function

jonathandienst — Tue, 01 Aug 2017 12:36:34 GMT

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;

Re: Pick and Match function

effinty2112 — Tue, 01 Aug 2017 12:45:37 GMT

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

Re: Pick and Match function

sunny_talwar — Tue, 01 Aug 2017 12:51:12 GMT

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

];

Re: Pick and Match function

Kushal_Chawda — Tue, 01 Aug 2017 12:56:21 GMT

see this

If Condition?

Re: Pick and Match function

ashis — Wed, 02 Aug 2017 05:00:03 GMT

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

Re: Pick and Match function

jonathandienst — Wed, 02 Aug 2017 09:22:45 GMT

@sunny - clever code!

Re: Pick and Match function

sunny_talwar — Wed, 02 Aug 2017 16:12:30 GMT

Thanks jontydkpi

Re: Pick and Match function

sunny_talwar — Wed, 02 Aug 2017 16:13:56 GMT

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?

Re: Pick and Match function

ashis — Thu, 03 Aug 2017 06:40:56 GMT

Yes , thank you.