Formulas and best practices

1. Formulas and best practices

Now, let’s expand our knowledge of the Formula tool further! Let’s update the RecordID field and add three zeros to the right to make six characters. First, let’s check the data type of this field. The RecordID field is numeric, but the function we want to use, PadRight, is a String function. To convert the RecordID, add a Select tool after the True output of the Filter tool. Change to a V_WString, and now add a Formula tool. Use Select Column and select the RecordID field to update that column. Go to the Functions button, then the String category. PadRight is a length-based function, and click to add it to the expression builder. The String highlighted is the field we are updating. Click on the Columns and Constants button and select RecordID. The length is the total length of the ID, no matter the existing number of characters, so we want to type “6”. The character being padded is ‘0’ in quotes. Click run, and now the RecordIDs have a total of 6 characters. Next, after the referee names, there’s a 3-digit location. Let’s extract that and create a new field called “Referee Nationality”. Add another Formula tool, and select “Add Column”. Type ”Referee Nationality”. Use another String function and choose the Right function to obtain the right three characters. The string is “Referee”, and the length is “3”. Change this to a V_WString size 8. We’ve added the new field with the referee nationality. When written correctly, your data preview will show the first record. Let’s find the number of years since the games were played. Here, we have the DateTime field in a DateTime format. The Current Year is a numeric format. Let’s obtain the year from the DateTime field and convert it to a number. With the same Formula tool, click on the blue square with the plus sign to add a new formula. Add a new column called “Year”. Use a DateTime function of DateTimeYear(). The field is DateTime, showing 1986 for the preview. Setting to Double allows us to perform numeric functions such as subtractions. Click the plus sign and create a column called “Number of Years”. // Add “Current Year” minus “Year”. Now you can see it has been 38 years since 1986 for the game in record one. Let’s flag if a win was based on extra time. Make a new column called “Flag Extra Time”. Go to the String functions and select “Contains”. With “String” highlighted, click “Win Conditions”. The target is “extra time” in quotes. Change it to a V_WString size 8. The result is either 0 or -1, which flags the win by extra time. Let’s view a position-based formula. In DateTime, we have year-month-day. We can extract the month from the middle using a Substring function. Make a new column called “Month”. Now, use the String functions. Similar to Substring is GetWord, and when you hover, it says, “returns the nth (0-based) word in a string”. Click Substring, and the field is Datetime. We start counting at 0 for position-based, so we count 0, 1, 2, 3, dash is 4, 5, 6. We start on 5 with a length of 2 characters to create the month. Make it a V_WString size 8. The result is either June or July. So that’s a tour of the Formula tool! Let’s get to some exercises!

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.