topic Re: Re: Pick and Match in QlikView

Pick and Match

MK_QSL — Mon, 03 Feb 2014 15:39:38 GMT

I have enclosed sales data and want Type in Straight Table based on CustType Selection.

Please note that I can't link CustType with Type.. Please don't ask why !!

Let me know how can i achieve using PICK and MATCH function?

Re: Pick and Match

jerem1234 — Mon, 03 Feb 2014 15:45:12 GMT

Don't need pick,match. You can use the p() function in set analysis:

SUM({<Type=p(CustType)>}Sales)

Please find attached.

Hope this helps!

Re: Pick and Match

MK_QSL — Mon, 03 Feb 2014 15:50:56 GMT

Yes but I want to show Type as a dimension based on CustType selection

Re: Pick and Match

Peter_Cammaert — Mon, 03 Feb 2014 15:53:43 GMT

Something like this? I'm not using either Match() or Pick().

Peter

Re: Pick and Match

jerem1234 — Mon, 03 Feb 2014 15:57:50 GMT

In my example, Type is a dimension and is being affected by selections in CustType. Just make selections in CustType and it will affect the table. Can you explain a little more about the behavior you're looking for if this isn't what you were looking for?

Re: Pick and Match

MK_QSL — Mon, 03 Feb 2014 16:02:57 GMT

Looks OK for me... Can you explain the function you used?

Re: Re: Pick and Match

jerem1234 — Mon, 03 Feb 2014 16:13:15 GMT

If you don't want the table to show anything if nothing is selected in CustType, then use:

if(GetSelectedCount(CustType)>0,SUM({<Type=p(CustType)>}Sales))

Re: Pick and Match

Peter_Cammaert — Mon, 03 Feb 2014 18:41:51 GMT

GetFieldSelections() gets the currently selected values from a specific field, as one string with each value separated by the string passed as second parameter. E.g. if you select Small, Very Small and Big in your CustType list,

= GetFieldSelections(CustType, ';')

will return the string 'Big;Small;Very Small'. Index() tries to find each and every dimension value in this string, and will suppress all other rows if you use it in a Calculated Dimension because of the IF() returning NULL().

The stuff with the leading and trailing semicolon is needed because selecting CustTyp = 'Very Small' would otherwise match 'Very Small' AND 'Small' which is not what you want. Therefor I'm forcing delimiters at the beginning and end of every value.

Note that GetFieldSelections() will return another, compressed value whenever the number of selected values is higher than some limit (default = 6) You can avoid this by including a third parameter with an unreasonably high value.

Good luck,

Peter