This post is about using Pick and Match functions for implementing
Multiple If or Switch case statements.
Consider an example:
We have field City and current GMT( Greenwich Mean time) . and there are 10 different Cities in City field.We need to calculate Current time in each City. So expression will be
If(City = 'New York', GMT-5,If(City = 'London', GMT,If(City = 'Delhi', GMT+5.30,If(City = 'San Francisco', GMT-8...)))) as CurrentTime
Or in User-Interface (UI)
CurrentTime = If(City = 'New York', GMT-5,If(City = 'London', GMT,If(City = 'Delhi', GMT+5.30,If(City = 'San Francisco', GMT-8...))))
Multiple Ifs are CPU and Memory intensive operation.
We can improve its performance using below expression.
CurrentTime = Pick( Match ( City, 'New york', 'London', 'Delhi', 'San Francisco', 'Tokyo', 'Tampa', 'Seattle', 'Paris', 'Helsinki',')
,GMT- 5,GMT,GMT+ 5.30,GMT-8,GMT+9,GMT-5,GMT-8,GMT+1,GMT+2)
Match:
Match(FieldName,'Fieldvalue1','Fieldvalue2' ) : Returns position of matching Fieldvalue.
Match(City,'New york','Delhi') : will return 1 when City is New York and 2 when City is Delhi.
Pick:
Pick(Index,Expression1,Expression2) : Returns result of Expression which Position= Index.
Pick(1,2+3,2+2) returns 5.
In expression above Match function will return Index corresponds to City.
Index =Match(City,'Newyork','London','Delhi','California','Tokyo','Tampa','Seattle','Paris','Helsinki'').
And Pick will return result of Expression at position = Index
Pick(Index,GMT- 5,GMT,GMT+ 5.30,GMT-8,GMT+9,GMT-5,GMT-8,GMT+1,GMT+2)
