topic Re: Pick(Match()) or may be another function in QlikView

Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 12:23:59 GMT

Hey Qlikers,

There might be a very easy solution for this, but I am kind of confused on how I can achieve this. I have a formula like this:

=Pick(Match(23, 23, 34, 23, 45), 'R', 'T', 'P', 'K') and when I put this in a text box, I get the output to be R (which makes sense because its matching the first instance of the number to match with and giving me the output R.

What I want is to be able to somehow get the output R, P.

To give some details about what exactly I am doing:

=Pick(Match(MaxfromMultipleFieldsusingRangeMax, MaxField1, MaxField2, MaxField3.....), 'Field1', 'Field2', 'Field3'....)

All the responses are greatly appreciated.

Best,

Sunny

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 12:46:34 GMT

Just a thought: may be I can use Concat function with set analysis

Any ideas?

Best,

Sunny

Re: Pick(Match()) or may be another function

anbu1984 — Tue, 21 Apr 2015 12:58:34 GMT

=Pick(Match(23, 23, 34, 45), 'R, P', 'T', 'K')

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 13:02:43 GMT

Thanks for your response anbu cheliyan‌

But this was just an hard coded example, the actual values may vary. I am using functions instead of numbers here so I won't know what comes together. The function looks something like this:

=Pick(Match(MaxfromMultipleFieldsusingRangeMax, MaxField1, MaxField2, MaxField3.....), 'Field1', 'Field2', 'Field3'....)

Best,

Sunny

Re: Pick(Match()) or may be another function

anbu1984 — Tue, 21 Apr 2015 13:15:09 GMT

If you don't have many fields, then use if like

vMaxValue=MaxfromMultipleFieldsusingRangeMax

Replace(Trim(If(vMaxValue = MaxField1Expr,'Field1') & ' ' & If(vMaxValue = MaxField2Expr,'Field2') & ' ' & If(vMaxValue = MaxField3Expr,'Field3') ), ' ', ',')

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 13:18:40 GMT

I have 14 fields right now, but this is kind of a template app and it could increase up to 25 I think for other versions. I do have this as an alternative, but I thought there must be an easier way to do it. (may be using concat and set analysis?)

But once again thanks for your response.

Best,

Sunny

Re: Pick(Match()) or may be another function

Tue, 21 Apr 2015 14:10:24 GMT

You could do this with conat and set analysis yea, you would need a data island of your position value and result though I think, for that to work.

basic example attached

hope that helps

Joe

Re: Pick(Match()) or may be another function

stigchel — Tue, 21 Apr 2015 14:32:16 GMT

How about a variable function that you can call for each field. Something like the attached.

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 15:37:02 GMT

This looks like it could work, I just need to test it out for the application I am working on.

Thanks Piet Hein van der Stigchel‌

Best,

Sunny

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 16:01:51 GMT

Piet Hein van der Stigchel‌ Can I do something like this in the variable you created?

vIsMax

If(Max({<Year = {2015}>}[$1]) - Max({<Year = {2014}>}[$1]) = RangeMax(Max({<Year = {2015}>}Field1) - Max({<Year = {2014}>}Field1), Max({<Year = {2015}>}Field2) - Max({<Year = {2014}>}Field2), Max({<Year = {2015}>}Field3) - Max({<Year = {2014}>}Field3)),Right('$1', 2),'')

and then use the variable like this

$(vIsMax(Field1))&$(vIsMax(Field2))&$(vIsMax(Field3))

Best,

Sunny

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 16:03:01 GMT

It did work, made a small error which was causing it to not work. But thanks for introducing this new concept to me.

Best,

Sunny

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 16:03:57 GMT

Thanks for your response Joe, but in my scenario this wouldn't be very helpful to use this.

Best,

Sunny

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 16:16:06 GMT

Out of curiosity, can I Give more than one inputs to a function? May be like

vVar = Avg([$1]) - Avg([$2])

and use is like this? $(vVar(Field1, Field2))

Re: Pick(Match()) or may be another function

anbu1984 — Tue, 21 Apr 2015 16:19:36 GMT

Yes you can pass more than one argument

Re: Pick(Match()) or may be another function

sunny_talwar — Tue, 21 Apr 2015 16:42:22 GMT

Amazing! Thanks guys

Best,

Sunny