Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vinay_bangari
Valued Contributor III

purpose of pick and match.

Please help me understanding how pick and match works.

1 Solution

Accepted Solutions

Re: purpose of pick and match.

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]

9 Replies

Re: purpose of pick and match.

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

vinay_bangari
Valued Contributor III

Re: purpose of pick and match.

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

datanibbler
Esteemed Contributor

Re: purpose of pick and match.

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

Not applicable

Re: purpose of pick and match.

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.

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.

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

vinay_bangari
Valued Contributor III

Re: purpose of pick and match.

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

Re: purpose of pick and match.

No problem Vinay

I will look forward for your call.

prasadcm
Contributor II

Re: purpose of pick and match.

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?

Community Browser