Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Please help me understanding how pick and match works.
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]
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
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
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
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
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]
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
Thanks a lot for all your suggestion guys. That helped me .. Sunny i will call you man.
No problem Vinay
I will look forward for your call.
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?