9 Replies Latest reply: Mar 2, 2016 6:28 AM by prasad mayekar RSS

    purpose of pick and match.

    Vinay kumar Bangari

      Please help me understanding how pick and match works.

        • Re: purpose of pick and match.
          Sunny Talwar

          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.
              Vinay kumar Bangari

              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.
                  Sunny Talwar

                  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

                      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.
                        prasad mayekar

                        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?

                    • Re: purpose of pick and match.
                      Friedrich Hofmann

                      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.

                        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