|
|
|
|
 Home > Resource Centre > Tips & Tricks > Functions
|
 |
Example 1 – Sub String Function
Example 2 – Left (and Right) functions
Example 3 – DayName function
Example 1 – Sub String Function
Problem
You have a created a report with Course Codes in it eg COMP1050. You would like to create a variable to show the “year” of each course, which is the first numeric character in the Course Code, eg COMP1050 is a first year course, COMP3000 is a third year course. There is no object in the universe (or indeed, any field within SI-net) to indicate “year of course”. Answer
You can create a variable with the SubStr (Sub String) function to show the first numeric character. Solution
Create a variable.
- Go to Data, then Variables. Click on the Add button.
- Now that the variable editor is open, go to the Definition tab and name the variable (eg Year of Course). NB: If you do not name the variable, a formula is created instead.
- Go to the Formula tab to create a formula. To create the formula, select from the list of Operators, the list of Functions, and also the list of Variables – in this case, the formula is: =SubStr(<Course Code> ,5 ,1). Select the = from the list of Operators to start, then the SubStr function from the list of functions, then Course Code is selected from the list of Variables.
|
|
So, how does the Sub String function work?
Sub String takes a sequence of characters from a character string - SubStr(character string, numberStart, numberLength).
The first number represents the position of the first character in the string that you want to take out. The second number represents the number of characters to extract, beginning with the first number. In our example, the first number is 5, which is the 5th character in the Course Code eg 3 in COMP3000 (3 is the 5th character). The second number is 1 character long, so the result is “3”.
Another example is if we created the formula =SubStr(<Course Code>,1,4) – for COMP3000 we would return the first 4 characters, ie COMP. See example below for another way of solving this example. | |
- Once created, the variable can be used in the table.
Example 2 – Left (and Right) functions
Problem
You have a created a report with Course Codes in it eg COMP1050. You would like to create a variable to show first 4 characters of each course eg COMP for COMP1050, or ENGL for ENGL2000. Answer
You can create a variable with the Left function to show the characters. Solution
Create a variable.
- Go to Data, then Variables. Click on the Add button.
- Now that the variable editor is open, go to the Definition tab and name the variable (eg Alpha Course Codes)
- Go to the Formula tab to create a formula. The formula is: =Left(<Course Code> ,4). Select the = from the list of Operators to start, then the Left function from the list of functions, then Course Code is selected from the list of Variables.
|
|
So, how does the Left function work?
Left takes out the specified number of characters from the left of the character string - Left(character string, number of characters)
In our example, the number used is 4, which takes out the first 4 characters in the Course Code eg COMP for COMP3000 or ENGL for ENGL2000.
The Right function works in the same way, but takes characters to the right of the character string. If we created the formula =Right(<Course Code>,4) we would see the last four characters of the course code eg 3000 for COMP3000 or 2000 for ENGL2000. | |
- Once created, the variable can be used in the table.
Example 3 – DayName function
Problem
You have a created a report with a date (or dates) in it. You would like to know which day of the week this date falls on (eg Monday).
Answer
You can create a variable with the DayName function to show the day of the week. Solution
Create a variable.
- Go to Data, then Variables. Click on the Add button.
- Now that the variable editor is open, go to the Definition tab and name the variable (eg Day of Week)
- The date in my report is called “Application Date”. Go to the Formula tab to create a formula. The formula is: =DayName(<Application Date>). Select the = from the list of Operators to start, then the Left function from the list of functions, then Course Code is selected from the list of Variables.
- Once created, the variable can be used in the table.
|
|
|
|