Business Intelligence Tools

Spotfire Expressions – $esc and $map functions

  • Do you want to learn more about the Spotfire expression language?
  • Are you confused by expressions with $map and $esc functions?

The $map and $esc functions are two of the most used functions in Spotfire custom expressions.  They perform very simple functions that aren’t easy to decode from looking at examples.

Let’s start with $esc.  This function is simply enclosing a string in brackets so Spotfire will interpret it as a column name. In the example shown below, there are two list box (single select) property controls setup in a text area to control the x and y axis.  Controls are attached to the axis by right clicking on the axis selectors and selecting ‘Set from property’.

example-1

Generally speaking, anytime a variable(s) is added to a visualization, under the covers, Spotfire is writing a custom expression.  Even though I didn’t actually write an expression, by right clicking on the x axis and selecting ‘Custom Expression’, the expression the application wrote becomes visible.

In this case, the expression references my list box property, which is named ‘Listbox’.  The $esc function is wrapping it in brackets, so Spotfire will interpret it as [Reservoir].  The carats indicate the variable should be interpreted as a categorical variable rather than a continuous variable.  You could remove the carats, and the expression would work, but the graph would look funny.

<$esc(${Listbox})>

example1a

In this particular case, I was able to use the right click, ‘Set from property’ menu option, but this isn’t always possible and knowing how to pass a string value through as a column name is very useful.

Next, let’s look at the $map function.  This function is commonly used with List Box (multiple select) properties.  It provides a template to create a custom expression for multiple selections so the expression will work no matter how many selections are made.

In order to better understand this, I created a simple bar chart (not shown) with two variables on the y axis.  Then, I right clicked and selected ‘Custom Expression’ to see the resulting expression (shown below).  When multiple variables are added to the y axis of a Spotfire visualization, the individual expressions are separated by a comma.  If more variables were added, there would be more aggregations and column names separated by commas.

example2a

Now, let’s go back to $map.  In the example below, I have created two property controls.  This time, the property control is a List Box (multiple select), rather than a single select.  The control is called LBM1.  Again, right click on the y axis selector and choose ‘Set from Property’ to attach the property to the y axis.

example3

If I then right click on the y axis and select ‘Custom Expression’, the resulting expression is as follows:

example3a

…step by step, what the expression is doing is…

  1. The $esc() function is putting brackets around the selections in the list box, which in the case is [Oil Prod] and [Gas Prod].
  2. The sum aggregation was picked up because it was the aggregation on the y axis when I connected the  visualization to the property control.  It could easily be replaced with a different aggregation.
  3. The second part of the $map function is the “,”.  This is telling the expression to put a comma BETWEEN selections in the list box.  In other words, it’s creating this expression:

sum([Oil Prod]), sum([Gas Prod])

This is EXACTLY what we saw on the y axis custom expression after simply adding two variables to the y axis.

example2a

Now, I didn’t have to write the $map function.  Spotfire did it for me when I right clicked and selected ‘Set from Property’.  This won’t always be the case, so here is an example where two property controls are incorporated on the y axis, and the user MUST write a custom expression.

In the screenshot below, one control gives the user the ability to select which variables appear on the visualization and the other specifies the aggregation method.  In this case, clicking ‘Set from Property’ won’t work, and I have to write a custom expression on the y axis.

example3d

The Aggregation drop down was created with Fixed values.  Note, the display name may be any name, but the Value must match the Spotfire function exactly.

example3e

example3b

The expression is written as follows:

  1. Right click on the y axis, choose ‘Custom Expression’
  2. Start the expression with the $map function
  3. Put quotes around the expression that Spotfire will read
    1. ex. “sum(${LBM1})”
    2. ex. “${Aggregation}($esc({$LBM1})”
  4. Replace Sum (the old aggregation), with the drop down property name –> ${Aggregation}
  5. Wrap the $esc function in parenthesis because all aggregations/functions are wrapped in parenthesis
    1. ex. sum([Oil Prod])
    2. ex. min([Gas Prod])
  6. Reference the List box (multi select) property control in the $ esc function  –> $esc(${LBM1})
  7. Close the quotes and parenthesis
  8. Separate the first argument from the second with a comma
  9. Specify how the expressions should be separated, which is with a comma.  This part must also be enclosed in quotes.

DONE!  Now you should be aware of what $esc and $map are doing.  Hopefully this will help you better understand Spotfire expressions.

 

10 thoughts on “Spotfire Expressions – $esc and $map functions

  1. Tyler Palmer Reply

    Julie,
    Question on the second bar chart (the one with “Sum(OilProd)” and “Sum(GasProd)” on the y-axis). How would you manipulate the custom expression so that the y-axis can say just “OilProd” and “GasProd”, but not show/specify the Sum aggregation?

    Thanks- Tyler Palmer

    • Julie Reply

      Use the keyword ‘as’ in a custom expression. Right click on the y axis, select Custom expression. After you see Sum([OilProd]), enter ‘as [OilProd]’. The keyword ‘as’ followed by what you want to call it, wrapped in square brackets will get what you want.

  2. Pingback: Spotfire Expressions — how to return a column name from a property control rather than the column values and a Spotfire Description hack |

  3. K Reply

    I am trying to use the map function to create an expression where the property value ends up as text.

    This expression: if($map(“[COLUMN]=(${My.Property.List})”, ” or “), . . . .)
    Returns this expression: if([COLUMN]=(List Value A) or [COLUMN]=(List Value B), . . . . )
    What I want is: if([COLUMN]=”List Value A” or [COLUMN]=”List Value B”, . . . .)
    (The ellipses would be the subsequent calculations for the IF statement.)

    Replacing the parentheses with quotation marks makes the the expression invalid.
    Any thoughts? Thanks in advance!

    • Julie Reply

      I don’t think the map function will work in this case. To explain, create a calculated column just referencing the property control. I have a list box multi select property control called Test with two values ‘Test 1’ and ‘Test 2’. The property control returns ‘Test 1, Test 2’. In order to use the property control in your if statement as desired, you need to get rid of that comma between the two values, which I don’t think you can do with the $map function. You could create two calculated columns with these expressions — Left([c.Property Value],Find(“,”, “${Test}”)-1) AND Right([c.Property Value],Len([c.Property Value])-Find(“,”, “${Test}”)). c.Property Value is my calculated column that references the property control. Now you have two columns you can reference in the desired If statement. This will work.

      • K Reply

        I was actually able to do it using single quotes:

        This expression: if($map(“[COLUMN]=’${My.Property.List}’”, ” or “), . . . .)
        To get: if([COLUMN]=’List Value A’ or [COLUMN]=’List Value B’, . . . .)

        I didn’t realize single quotes could also denote text.

  4. eddie Reply

    how to use $csearch funtion?
    it doesn’t like the Data Table name passed into it.

Leave a Reply

Your email address will not be published. Required fields are marked *