Only Records Meeting Certain Criteria
Example 5 - List mode: show a list of only those records meeting a set criterion
Other features illustrated: Advanced SQL
Background: You can have your table only show results for the records that meet your criteria. For example,
if you want to see all the counties within a select number of states that have a population within a certain size, you can
get those listed in a table and include additional information from the dataset.
- Create a listing of all the counties in Alabama, Georgia, and Florida that have a population of less than 75,000 people.
- Show the actual population estimate and the median household income for those counties.
Steps: Goal 1 Create a listing of all the counties in Alabama, Georgia, and Florida that have a
population of less than 75,000 people.
- Open the Ferrett Session File (or saved data basket) named
- In the data basket, delete the FIPS County variable
- Go to the step 1 tab. You will notice that there is a required variable - Selectable Geographies.
- Double click on this variable. This brings up the geography selection window. Select all the counties in Alabama,
Florida, and Georgia.
- In the step 1 tab, go to the "Geographic Traits" topic listed under Summary File 3 and view all the variables.
- Sort the list by variable name and select the "NAME" and the "STUSAB" variables. These two variables will give us
geographic names for our list.
- Select the "P001001" variable from the P1. Total Population topic in SF3.
- Go to the step 2 tab in order to set our population limit.
- Click on the "Advanced Sql Option" button on the right side. This brings up the Advanced Sql Option window.
Screenshot of Advanced Sql Option window (Click figure to Enlarge)
- Insert text as shown in the highlighted portion of the image. This will limit the counties in our list to those with
a population under 75,000.
- Press the "Ok" button.
- "Make a Table" and close the green-bordered instruction window.
- Go to the "Options" menu and select "List Mode". When creating a table in "List Mode", you will always leave column 1 empty.
- Drag the "NAME" variable to column 2 (C2, R1).
- Drag the "STUSAB" variable to column 3 (C3, R1).
- "Go Get Data" and your result is a list of the county names and state abbreviations for all of the counties in the
selected three states that have a population less than 75,000.
Steps: Goal 2 Show the actual population estimate and the median household income for those counties.
We can also view other variables in our list, such as the population and median household income.
- Turn off the List Mode - "Options" menu, select "List Mode".
- Drag the P001001 (population) variable into column 4 (C4,R1).
- Drag the P053001 (median income) variable into column 5 (C5,R1).
- "Go Get Data"