load
load
load
load
load
load
load
load
Sugarcane will be shutting down on June 30, 2018. For more information, .

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.