topic Re: purpose of pick and match. in QlikView

purpose of pick and match.

Anonymous — Thu, 07 May 2015 14:04:18 GMT

Please help me understanding how pick and match works.

Re: purpose of pick and match.

sunny_talwar — Thu, 07 May 2015 14:08:20 GMT

Vinay I think the purpose of using Pick and Match together is to simplify long if statements. For example

If(ABC = 'A', 1st Expression, If(ABC = 'B', 2nd Expression, 3rd Expression) can be simplified if done this way:

Pick(Match(ABC, 'A', 'B', 'C'), 1stExpression, 2ndExpression, 3rd Expression)

Here we only had 3 if statement, but if we have 10 then pick makes it really easy to read and understand from somebody who is trying to understand what is going on in the expression.

HTH

Best,

Sunny

Re: purpose of pick and match.

Anonymous — Thu, 07 May 2015 14:11:29 GMT

can you please tell me how this is working..

floor(pick(Match(Status, 'Assigned', 'Canceled', 'Closed', 'In Progress', 'Pending', 'Resolved'), today(), [Resolved Date], [Resolved Date], today(), today(), [Resolved Date]) - [Submit Date]) as age

Re: purpose of pick and match.

datanibbler — Thu, 07 May 2015 14:12:04 GMT

Hi,

yes, exactly. Imagine you have a very long formula made up of, say, seven nested IF_clauses. That makes for a pretty complicated and long formula and a lot of opening brackets which are to be closed only at the very end of it all.

=> With PICK(MATCH()), it is much easier because every line of the formula (every MATCH, equivalent of one IF_part)
has its own pair of brackets. That much reduces the potential for errors in the formula.

Best regards,

DataNibbler

Re: purpose of pick and match.

Thu, 07 May 2015 14:13:47 GMT

1. Match : Match will give you a position number for serch string object.

E.x. Match('B','A','B','C') = 2

2. Pick : Pick will give you a search object as per the position number.

E.x. Pick(1,'A','B','C') = A

Re: purpose of pick and match.

sunny_talwar — Thu, 07 May 2015 14:14:53 GMT

In simple words your expression is doing this:

if Status is Assigned or In Progress or Pending then age = Today() - [Submit Date]

if Status is Canceled or Closed or Resolved then age = [Resolved Date] - - [Submit Date]

Re: purpose of pick and match.

sunny_talwar — Thu, 07 May 2015 14:18:33 GMT

Easier way to write the above expression would be to do this:

If(Match(Status, 'Assigned', 'In Progress', 'Pending',), Today(), [Resolved Date]) - [Submit Date] as Age

HTH

Note: Assuming that Status can only take the given 6 values.

Best,

Sunny

Re: purpose of pick and match.

Anonymous — Thu, 07 May 2015 14:24:29 GMT

Thanks a lot for all your suggestion guys. That helped me .. Sunny i will call you man.

Re: purpose of pick and match.

sunny_talwar — Thu, 07 May 2015 14:25:38 GMT

No problem Vinay

I will look forward for your call.

Re: purpose of pick and match.

prasadcm — Wed, 02 Mar 2016 11:28:37 GMT

Hi,

i have an expression like following

=if(([Flip by Source No_]=1 AND [Flip by Business Line]=0 AND [Flip by Company]=0 AND [Flip by IC Partner]=0 AND [Flip by VAT Bus_ Posting Group]=0), //condn 1 source NO

if([Balance Flip Sign]=1,

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Source No_])<0, [Flipping Account No_],[GL account code]),

//else

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Source No_])>0, [Flipping Account No_],[GL account code])

//else condn 2

if(([Flip by Source No_]=0 AND [Flip by Business Line]=1 AND [Flip by Company]=0 AND [Flip by IC Partner]=0 AND [Flip by VAT Bus_ Posting Group]=0), //condn 2 Business Line

if([Balance Flip Sign]=1,

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Business Line])<0, [Flipping Account No_],[GL account code]),

//else

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Business Line])>0, [Flipping Account No_],[GL account code])

//else condn 3

if(([Flip by Source No_]=0 AND [Flip by Business Line]=0 AND [Flip by Company]=1 AND [Flip by IC Partner]=0 AND [Flip by VAT Bus_ Posting Group]=0), //condn 3 Company

if([Balance Flip Sign]=1,

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Entity Name])<0, [Flipping Account No_],[GL account code]),

//else

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[Entity Name])>0, [Flipping Account No_],[GL account code])

//else condn 4

if(([Flip by Source No_]=0 AND [Flip by Business Line]=0 AND [Flip by Company]=0 AND [Flip by IC Partner]=1 AND [Flip by VAT Bus_ Posting Group]=0), //condn 4 IC Partner

if([Balance Flip Sign]=1,

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[IC Partner Code])<0, [Flipping Account No_],[GL account code]),

//else

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[GL account code],[IC Partner Code])>0, [Flipping Account No_],[GL account code])

//else condn 5

if(([Flip by Source No_]=0 AND [Flip by Business Line]=0 AND [Flip by Company]=0 AND [Flip by IC Partner]=0 AND [Flip by VAT Bus_ Posting Group]=1), //condn 5 VAT BUS

if([Balance Flip Sign]=1,

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[VAT Bus_ Posting Group])<0, [Flipping Account No_],[GL account code]),

//else

if(AGGR(nodistinct sum(distinct [Additional Currency Amount]),[VAT Bus_ Posting Group])>0, [Flipping Account No_],[GL account code])

),)))))

How am I supposed to put these in pick match function?