Field calculations allow the hosted feature layer owner or portal administrator to alter the values of every row for a single field in the attribute table of a layer.
For example, if you have a hosted feature layer that stores information on property sales—including the sale price and tax rate for the property location—you could add a field to the layer to store estimated property taxes. To populate the estimated_property_taxes field, define a calculation expression for the field that takes the values in the sale_price field and multiplies them by the tax_rate values.
Follow these steps to calculate string, numeric, or date field values in a field.
Note:
Field calculations cannot be undone. For this reason, a good practice is to add a field, calculate values into it, and confirm the calculation is what you wanted. If it is, you can then calculate the original field to equal the added field. Once you confirm the values are correct in the original field, you can delete the field you added.- From the layer's item page, click the Data tab to show the table.
Note:
You can also perform these steps from the table in Map Viewer.
- Click the column containing the values you want to calculate.
- Do one of the following to open the Calculate Field dialog box:
- Click Calculate.
- Click Show Detailed View > Calculate
- Use basic operators, a field list, and functions to compose the calculation expression.
- Once you compose your expression, click the Validate button to ensure there are no errors in it. If the expression is invalid, click the Remove button and compose a new one.
- To apply the calculation to all values in the field, click Calculate.
The time it takes for the calculation to complete depends on the expression's complexity and the number of features in your layer.
Basic operators
On the Calculate Field dialog box, you can build simple SQL expressions using operators such as plus, minus, multiply, and divide. Examples and tips for using these operators are as follows:
- To multiply all values in a numeric field named SAMPLE by 100.0, type SAMPLE * 100.0 for the expression.
- For more complex equations, you can use the parentheses to specify the order of calculations, for example, SAMPLE * (BASELINE - 40).
- Math operators do not work with string fields. You'll need to use the string functions described in the String functions section.
- If you are calculating a field of type double to field of type integer, the CAST function may be automatically added to your expression. For example, if you're calculating a double field named POP to an integer field named SAMPLE, the expression will appear as CAST(SAMPLE AS FLOAT). Do not remove the CAST function. See Numeric functions below for information on the CAST function.
- To include an apostrophe in the string, use two single quotation marks for the apostrophe. For example, 'Nightingale''s'. Do not use a double quotation mark.
After you've created your expression, click the Calculate button. If there are any errors, an error message will appear at the bottom of the dialog box.
The following are additional considerations:
- If you have a filter on your layer, only the records that meet the filter criteria will have their values calculated.
- Calculate Field works only with field names, not field aliases. The Fields list shows you all the field names available for calculations. You can filter this list by the field types String, Numeric, and Date.
- If you hover over a field name in the Fields list, the field alias and field type are displayed.
- If you click a field name in the Fields list, the field is added to the expression.
Functions
In addition to simple expressions using operators, you can also use functions to build SQL expressions. Functions work with field names, literals, and other functions. For example, suppose you need to calculate a double field to be TOTALPOP divided by POP18. If any feature has a POP18 equal to zero, the calculation will result in a divide-by-zero error. You can guard against this using the NULLIF function described below. The expression would be TOTALPOP / NULLIF(POP18, 0).
Functions take arguments. In the tables below, any argument can be as follows:
- A field name, as long as the field type matches the argument type (string, number, or date).
- A literal, such as 'Sailboat' (a string surrounded by single quotation marks), the number 5, or a date in MM/DD/YYYY hh:mm:ss format, surrounded by single quotation marks.
- A function that returns a value of the proper type (string, number, or date). For example, FLOOR(POWER(SAMP_ERR, 0.5)) returns the largest integer that is less than or equal to the square root of SAMP_ERR.
For illustrative purposes, the examples in the description column of the following tables mostly use literal arguments. You can substitute a field name or another function for these arguments.
String functions
| Function | Description |
|---|---|
CHAR_LENGTH(<string>) | Returns the number of characters in the specified string. The result is an integer. Example
|
CONCAT(<string1>, <string2>) | Concatenates two string values. Only two strings can be provided. To concatenate more than two strings, nest consecutive CONCAT functions as shown in the example below. Example
Null values are converted to an empty string. |
POSITION(<substring> in <string>) | Returns the position of the first occurrence of the specified substring in the string you specify. If the specified substring is not found, the result is 0. Examples
|
SUBSTRING(<string>, <start>, <length>) | Returns a part of a string value; <start> is an integer index specifying where the returned characters start, and <length> is the number of characters to be returned. Example
|
TRIM(BOTH | LEADING | TRAILING ' ' FROM <string>) | Returns a string where all leading or trailing spaces are removed from the string you specify. Example
Note that the second argument is two single quotation marks with a space between them. |
UPPER(<string>) | Returns a string where all characters are converted to uppercase. Example
|
LOWER(<string>) | Returns a string where all characters are converted to lowercase. Example
|
Numeric functions
| Function | Description |
|---|---|
ABS(<number>) | Returns the absolute (positive) value of the number you specify |
CEILING(<number>) | Returns the smallest integer greater than or equal to the specified. Example
|
COS(<number>) | Returns the trigonometric cosine of number, which is assumed to be an angle in radians. |
CAST(<number>AS FLOAT | INT) | Converts a number to a different type. FLOAT converts the specified number to a double and INT converts it to an integer. |
FLOOR(<number>) | Returns the largest integer that is less than or equal to the specified number. Example
|
LN(<number>,<decimal_place>) | The natural logarithm of the specified number. |
|
LOG(<number>,<decimal_place>) | The base-10 logarithm of the specified number. |
MOD(<number>, <n>) | Returns the remainder after the dividend (<number>) is divided by the divisor <n>. Both <n> and <number> must be of type integer. Example
|
NULLIF(<number>, <value>) | Returns null if the specified number equals the specified value. NULLIF is commonly used to prevent divide-by-zero errors by setting <value> to 0. Whenever a calculation encounters a null field value in any of its arguments, the result of the calculation is null. For example, suppose you need to calculate a double field to be TOTALPOP divided by POP18. If any feature has a POP18 value that equals zero, the calculation will result in a divide-by-zero error. You could create a filter to hide records where POP18 is zero, and perform your calculation. A shortcut is to use NULLIF.
|
POWER(<number> , <y>) | Returns the value of the specified number raised to the specified power (<y>). |
ROUND(<number> , <length>) | Rounds the number you specify to the specified length. If you use a positive number for the <length>, the number is rounded to the decimal position specified by <length>. When <length> is a negative number, the specified <number> is rounded on the left side of the decimal point. Examples
|
SIN(<number>) | Returns the trigonometric sine of the specified <number>, which is assumed to be an angle in radians. |
TAN(<number>) | Returns the tangent of the specified <number>, which is assumed to be an angle in radians. |
TRUNC(<number>,<decimal_place>) | Truncates the <number> at the specified <decimal_place>. A positive <decimal_place> truncates to the decimal position specified. When <decimal_place> is a negative number, the <number> is truncated on the left side of the decimal point. Examples
|
Date functions
Several calculations can be performed on date fields. For example, you can add or subtract time from a date field or calculate the difference between two date fields.
When working with date fields, keep these important considerations in mind.
You can use any combination of date and number fields and literals when calculating date fields. When using date literals, you must use SQL-supported date formats.
The following date functions are available:
| Function | Description |
|---|---|
CURRENT_DATE | Returns the current date in UTC time. |
CURRENT_TIMESTAMP | Returns the current UTC date and time (hours, minutes, seconds). |
EXTRACT(<unit> FROM '<date>') | Returns a single part (<unit>) of the specified <date>. Possible <unit> values include but are not limited to year, month, day, hour, and minute. For example
|
Add time to or subtract time from a date
You may want to add time to or subtract time from a date field to produce an updated date field. For example, you can calculate a future inspection or review date by adding time to a date.
Use an INTERVAL query to add time to or subtract time from a date field, as shown in the following calculations. The first example adds three days to a date to result in a new date. The second example subtracts three days from a timestamp field.<DateField> + INTERVAL '3'DAY = updated date<DateField> - INTERVAL '3 00:00:60' DAY TO SECOND = updated date
You can use INTERVAL with the following date and time values:
- DAY
- HOUR
- MINUTE
- SECOND
- DAY TO HOUR
- DAY TO MINUTE
- DAY TO SECOND
- HOUR TO MINUTE
- HOUR TO SECOND
- MINUTE TO SECOND
Calculate the difference between two dates
You might want to calculate the length of time between two dates. For example, if you have the installation dates for electric meters and you also have inspection dates, you can calculate the difference between the two dates to verify that the length of time between installation and inspection falls within allowable guidelines. The result of the calculation is a number field rather than a date field.
Any combination of date fields and date literals can be used to calculate the length of time between two dates. The first calculation below uses a date field, while the second uses a date literal. The third and fourth calculations use both a date field and a date literal.<DateField1> - <DateField2> = number of days in betweenDATE'<SQL-supported Date Literal>' - DATE'< SQL-supported Date Literal>' = number of days in between<DateField1> - DATE'<SQL-supported Date Literal>' = number of days in betweenDATE'<SQL-supported Date Literal>' - <DateField2> = number of days in between
The result is a number field that is calculated by subtracting one date field/literal from another date field/literal. The number result (in days) can be a whole number and can also include a fraction—for example, 1.5 would represent one-and-a-half days, or 36 hours.
In the electric meter inspection example mentioned above, any of the following calculations can be used to calculate the length of time between an installation date of 6/1/2015 and an inspection date of 10/1/2015. The first calculation uses date fields, the second uses date literals, and the third and fourth use both a date field and a date literal.<InspectionDateField> - <InstallationDateField> = 122 (days)DATE'10/1/2015' - DATE'6/1/2015' = 122 (days)<InspectionDateField> - DATE'6/1/2015' = 122 (days)DATE'10/1/2015' - <InstallationDateField> = 122 (days)
Restrictions when calculating field values
- You cannot calculate field values in copies of hosted feature layers, hosted feature layers that have related tile layers, hosted spatiotemporal feature layers, or views created from hosted spatiotemporal feature layers.
- Only standardized SQL queries are supported when calculating field values.
- You cannot use the numeric MOD function on double fields. Cast the field to an integer as shown in the example.