|
Tactician Techniques
The Value of SubString
We are hearing from more and more of you that you are taking advantage of Tactician One’s improved speed and capacity by working with larger data sets. Did you know you can use the Calculate command and System Functions to easily subset and manipulate your data?
We would like to take this opportunity to point out some key functions and tools in the Calculate function that can allow you to quickly select targeted subsets of data and/or cartography.
The first function we would highlight is the SubString function. This valuable feature allows you to take any data variable and return only a portion of its string value. This feature is extremely useful for separating data by a code or a type, breaking apart codes like a FIPS code to get the two digit state code, Carrier Route ID to get the 5 digit ZIP or Territory code to pull out the district and region codes into unique columns. It can also be used to prepare files for geocoding or other purposes where the city and state information are in the same column. Below are some common examples that we often see:
- Parameters for the SubString command:
Substring( Varname,start,char)
Where:
- Varname is the name of the column you wish to run the calculation against
- Start is the position number of the character that is your starting point – for instance the first character is position 1, the 5th character is 5 etc.
- Char is the number of characters to the right of your starting position you want to extract
Example 1:
Retrieving the 2 digit State FIPS code from a 5 digit County FIPS code
- In a data sheet that has a column for 5 digit county FIPS codes, select Document>Calculate and start a new calculation.
- Set the appropriate sheet (either main or other if applicable) to the sheet containing the code data.
- Set the new variable name to State or similar name of your own choosing.
- In the formula field, manually type or use the expression builder to create the following expression:
Substring(County,1,2)
Where county can be replaced with the name of your variable, if different.
When you run this calculation, Tactician creates a new variable column and populates the column with the first two characters of the county column data. For example:
County FIPS:
25005
25007
09012
07015
Would return the following State FIPS:
25
25
09
07
Example 2:
Splitting a City/State column to create a State Abbreviation column
Say you have a data set similar to the example below, and you wish to extract the 2-letter State abbreviation and create a new column for it.
City_State
Andover, MA
Wakefield, MA
Boston, MA
Peabody, MA
Malden, MA
Dover, NH
Exeter, NH
- Locate the column in the data sheet that contains the combined city and state information.
- Select Document>Calculate and start a new calculation.
- Set the appropriate sheet (either main or other if applicable) to the sheet containing the code data.
- Enter a name for the new column such as State or State Abbrev.
- Use the following calculation syntax:
substring(City_State,strlegnth -2, 2)
where City_State is the name of the variable in your document
What’s new here is the use of the strlength command to handle inconsistent city name lengths. This function allows for a different value for each record based on the length of city name. This function is extremely helpful when working with strings of variable size.
Example 3:
Using Substring to identify a data subset
Say you have a client data set where you have different types of clients and client IDs. In the Client ID, the 3rd character designates them as type 1, 2 or 3. You would like to create separate overlays in your maps that display the three types of client with a unique symbol.
The following process assumes you have created a points file and added it as a layer to an active Library in Tactician, making it available for mapping.
- Activate the map window of the document you wish to add the overlays to.
- Open the overlays dialog and click the + symbol to add a new overlay.
*Tactician’s new Insert feature allows you to add a new overlay just below the overlay you have selected in this dialog which can repositioning time.
- Select the layer that holds the points file of your client types and add it to your document.
- Select the overlay to modify it.
- In the Overlays dialog click the data tab.
- Leave the document selection as cartographic and the sheet type as main (this assume you have a standard points file in use).
- In the Condition for displaying records area, enter the following expression – substituting the starting position (3) and the value ( A) for the appropriate location and value in your file.
Substring($recid,3,1)=’A’
- When this expression is entered, only the A type clients from the layer will be displayed. You can use the style tab to create a specific look and feel for them.
- To display the other client types, repeat steps 2 through 8 for each client type, each time changing the Value (A) to the unique designation for each client.
Note that you are not limited to the $RecId column or to using just a single character to identify your unique point sets. You can also create joint overlays specifying more than one type using an expression like this:
substring($recid, 3,1) = 'A' OR substring($recid, 3,1) = 'C'
|
