Skip to main content

Define and Use Complex Expressions

The Define skill is a useful tool to define complex expressions. In this example, we'll explore how to use defined expressions to simplify using other skills, such as Keep and Visualize.

note

All steps in this How-To are completed using the Skill form. To enable the use of the Skill form in a session, see session settings

Load Our Data​

To start, load the file "sba_sample.csv" into the session.

SBA sample data

Predicate Expressions​

Define a Predicate Expression​

Let's say that we'd like to examine SBA data of California between the fiscal years of 2000 and 2012 where the NaicsCode isn't a null value. We could enter three separate steps to achieve this:

Keep the rows where BorrState contains CA
Keep the rows where ApprovalFiscalYear is between the values 2000 to 2012
Drop the rows where NaicsCode is null

Instead, we can define an expression to contain all of these conditions in a single step. First, click Skill in the skill menu, then enter:

Define a predicate expression CAapprovals that satisfies all of the following conditions BorrState contains CA, ApprovalFiscalYear is between the values 2000 to 2012, NaicsCode is not null

The conversation history returns a message stating that the predicate expression "CAapprovals" has been created.

Use a Predicate Expression​

We can now use our created expression, "CAapprovals", to view data that meets our conditions. In the Skill form, enter:

Keep the rows where CAapprovals

SBA dataset v2

We can see that our step returned a new dataset, "sba_sample v2", with 17 rows that met our given conditions.

Aggregate Expressions​

Define an Aggregate Expression​

Using the dataset "sba_sample v2", let's say that we'd like to visually compare the average gross approval amount against each SBA district. In this case, we need to create an aggregate expression to use before creating our visualization. In the Skill form, enter:

Define an aggregate expression <strong>AvGrossApproval</strong> as the expression average GrossApproval

The conversation history returns a message stating that the predicate expression "AvApproval" has been created.

Use an Aggregate Expression​

We can now use this expression to visualize the average gross approval amount for each SBA district. In the Skill form, enter:

Visualize AvGrossApproval by SBADistrictOffice

This generates a chart in the Chart tab, Chart1A, that looks something like this:

chart 1A

This chart shows us that the San Diego SBA district office held the highest average gross approval in California between the years of 2000 and 2012.