Wednesday, January 2, 2013

Using Pick and Match functions for Multiple ifs

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)