# How to Narrow Down Data

Only interested in the list of functions? Jump down to it.

## Narrowing Down Your Data

Data sets on Sugarcane sometimes can have too much information. Suppose you find a fantastic data set about countries of the world, but you’re interested in learning only about African countries. How can you study the capital cities of Botswana and Burundi but not of Brazil or Bulgaria? Fortunately, there is a way to do this. Using filters, you can eliminate data set rows that don’t fit your needs.

This is done in the game editor. You’ll click the ADD FILTER button and type an expression that tells Sugarcane which data set rows to use. Let’s look at an example. Here is a data set containing every country in the world.

Now, how do we narrow it down to the African ones? Notice that there is a column listing the continent each country belongs to. Using dollar signs, we can refer to this column name and say that it must equal “Africa”, like this:

Click PREVIEW, and the rows your filter refers to will be highlighted green. Your game will use only the green rows.

And here's the resulting game: Match Capital Cities of African Countries

To verify that it’s really using a filter, click the INSPECT button on the game start page. You’ll see exactly how the game was made.

Let’s consider another example. We want to make a game for putting the ten largest airports in order, but our data set includes 50 airports. We handle this by specifying that the value in the Rank (2013) column must be 10 or less.

And here is the game that results: Order the Ten Largest Airports

Filters can be as simple or complex as you’d like to make them. Next we’ll explore two examples that are a little more advanced. First, working with a Periodic Table data set, we’ll make a game about the noble gases. We want to learn about helium, neon, argon, krypton, and so on, except we do not want to include the obscure and unofficial ununoctium, which has atomic number 118. What we need is a filter that can narrow down our data set to noble gases having atomic numbers under 100. We’ll do this with an AND function. Within the parentheses, we list our two requirements, separating them by a comma.

Play or inspect this game here: Spell Noble Gases

In our final example, we’ll combine the AND function with a function called LEFT that allows us to match the leftmost characters of a word or phrase. Working with this data set of Oscar winners, we are going to narrow down the list to male and female actors whose first names start with A and who won their awards in the year 2000 or later. Let’s see it in action:

And here is our game: Match A Actors to Their Movies

By the way, the 1 used with LEFT tells Sugarcane to look at the *one* character at the very start of the phrase. We could have used a 2 to match the first two characters, or any other number.

Now you’re ready to try writing filters on your own. Continue reading for tips and tricks plus a complete list of functions that Sugarcane supports.

## How to Write a Filter

Remember these tips:

- Surround column names with dollar signs. For example, $Country Name$.
- Instead of typing the column name, you can make it appear by clicking the column name in the data set.
- Put text in double quotes. For example, "Africa".
- Make sure to follow every open parenthesis with a closed one!
- Function names and column names are not case sensitive. You don’t need to capitalize them. But you do have to keep any spaces.
- You can add an equals sign (=) to the beginning of your filter if you want. Some people like to do this, but it’s optional.

Finally, use the Inspect button to learn from others. Whenever you find a game that’s doing something interesting, click Inspect and find out how they did it!

Comprehensive List of Functions

To help you narrow your data down just the way you want it, we have a comprehensive list of functions for you to use. Here is a list of them all.

Function Name | Example Use | Explanation |
---|---|---|

EQ |
EQ(value1, value2) EQ(23, $col1$) EQ(“Red”, $col$) |
Returns TRUE if value1 is equal to value2 (ignoring case); FALSE otherwise. You can also use ‘=’, for example, value1=value2. |

EXACT |
EXACT(text1, text2)) EXACT(“Red”, $col2$) |
Returns TRUE if text1 and text2 are identical (case-sensitive); FALSE otherwise. |

NE | NE(value1, value2) NE(23, $col1$) NE(“Red”, $col$) |
Returns TRUE if value1 and value2 are not equal (ignoring case); FALSE otherwise. You also use “!=”, for example, value1 != value2. |

GT | GT(value1, value2) GT(23, $col1$) GT(“Red”, $col$) |
Returns TRUE if value1 is greater than value2; FALSE otherwise. You also use “>”, for example, value1 > value2. |

GTE | GTE(value1, value2) GTE(23, $col1$) GTE(“Red”, $col$) |
Returns TRUE if value1 is greater than or equal to value2; FALSE otherwise. You also use “>=”, for example, value1 >= value2. |

LT | LT(value1, value2) LT(23, $col1$) LT(“Red”, $col$) |
Returns TRUE if value1 is less than value2; FALSE otherwise. You also use “<”, for example, value1 < value2. |

LTE | LTE(value1, value2) LTE(23, $col1$) LTE(“Red”, $col$) |
Returns TRUE if value1 is less than or equal to value2; FALSE otherwise. You also use “<=”, for example, value1 <= value2. |

DATE |
DATE(year, month, day) DATE(2000, 1, 1) DATE(1960, 12, 31) |
Returns a Date using the year, month and day values given. |

TODAY | TODAY() | Always return the current Date. |

YEAR |
YEAR(date) YEAR($col1$) |
Returns the value of the data’s year as a Number. |

MONTH |
MONTH(date) MONTH($col1$) |
Returns the value of the data’s month as a Number. |

DAY |
DAY(date) DAY($col1$) |
Returns the value of the data’s day of month as a Number. |

ISBLANK |
ISBLANK(value) ISBLANK($col1$) |
Returns TRUE if the value is blank. |

ISERROR |
ISERROR(value) ISERROR($col1$) |
Returns TRUE if the value is an error. |

ISNUMBER |
ISNUMBER(value) ISNUMBER($col1$) |
Returns TRUE if the value is a Number. |

ISDATE |
ISDATE(value) ISDATE($col1$) |
Returns TRUE if the value is a Date. |

IF |
IF(logical_test, value_if_true, value_if_false) IF(ISNUMBER($col1$, “yes”, “no”)) |
If the logical_test is TRUE, returns value_if_true; otherwise, returns value_if_false. |

AND |
AND(logical1, logical2, …) AND(LT(23, $col1$), GT(123, $col1$)) |
If all of the arguments are TRUE, returns TRUE; otherwise, returns FALSE. There must be at least one value given. |

OR |
OR(logical1, logical2, …) OR(GT(20, $col1$), LT(120, $col1$)) |
Returns TRUE if any of the arguments is TRUE; returns FALSE otherwise. There must be at least one value given. |

NOT |
NOT(logical) NOT(ISNUMBER($col1$)) NOT(5=$col2$) |
Returns TRUE if logical is FALSE; returns FALSE otherwise. |

SUM |
SUM(num1, num2, ...) SUM(6, 3, 10) SUM(10, $col1$) |
Returns the sum of the values given. You can also use ‘+’, for example, num1 + num2. |

DIVIDE |
DIVIDE(num1, num2) DIVIDE(6, 3) DIVIDE(10, $col1$) |
Returns the quotient of the values given. You can also use ‘/’, for example, num1 / num2. |

PRODUCT |
PRODUCT(num1, num2, …) PRODUCT(6, 3, 10) PRODUCT(10, $col1$) |
Returns the product of the values given. You can also use ‘*’, for example, num1 * num2. |

POWER |
POWER(base, exponent) POWER(6, 3) POWER(5, $col1$) |
Returns the value of the base to the exponent. You can also use ‘^’, for example, base ^ exponent. |

MINUS |
MINUS(num1, num2) MINUS(6, 3) MINUS(10, $col1$) |
Returns the difference of the values given. You can also use ‘-’, for example, num1 - num2. |

ROUND |
ROUND(number, number_of_digits) ROUND($col1$, 5) ROUND(50.125, 1) |
Returns the number rounded to number_of_digits right of the decimal point. |

MAX |
MAX(num1, num2, …) MAX($col1$, $col2$, 500) |
Returns the greatest Number value out of the values given. |

MIN |
MIN(num1, num2, …) MIN($col1$, $col2$, 500) |
Returns the smallest Number value out of the values given. |

CONCATENATE |
CONCATENATE(text1, text2, …) CONCATENATE("hello", "world") CONCATENATE(10, $col1$) |
Concatenates the arguments in the order given. You can also use ‘&’, for example, text1 & text2. |

LEFT |
LEFT(text, number_of_characters) LEFT($col1$, 5) |
Returns the first number_of_characters characters in text. num_of_characters is optional and will default to be 1. |

RIGHT |
RIGHT(text, number_of_characters) RIGHT($col1$, 5) |
Returns the last number_of_characters characters in text. num_of_characters is optional and will default to be 1. |

MID |
MID(text, start_at, number_of_characters) MID($col1$, 1, 5) |
Returns number_of_characters characters from text, starting from start_at. The first character in the text has index 1. |

LEN |
LEN(text) LEN($col1$) LEN(“Hello”) |
Returns the number of characters in text. |

FIND |
FIND(find_text, within_text, start_at) FIND(“hello”, $col1$) |
Returns the number of the starting position of the first instance of find_text within within_text on or after start_at. start_at is optional. |

SUBSTITUTE |
SUBSTITUTE(text, old_text, new_text, instance_number) SUBSTITUTE(“They is happy”, “is”, “are”) |
Returns the text, with old_text replaced with new_text. instance_number is optional. If a number is specified, only that instance of old_text will be replaced. If unspecified, all instances of old_text will be replaced. |

ROWNUM |
ROWNUM(min_row, max_row) ROWNUM(2, 5) |
Returns true for rows whose row numbers are greater than or equal to min_row and less than or equal to max_row. |