Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

purpose of pick and match.

Please help me understanding how pick and match works.

1 Solution

Accepted Solutions
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]

View solution in original post

9 Replies
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

Anonymous
Not applicable
Author

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
Champion
Champion

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
Author

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

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]

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

Anonymous
Not applicable
Author

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

sunny_talwar

No problem Vinay

I will look forward for your call.

prasadcm
Creator II
Creator II

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?