# Functions In this section, you can find a list of functions available in Amazon Quick. To view a list of functions sorted by category, with brief definitions, see [Functions by category](https://docs.amazonaws.cn/quicksight/latest/user/functions-by-category.html). **Topics** + [ # addDateTime ](addDateTime-function.md) + [ # addWorkDays ](addWorkDays-function.md) + [ # Abs ](abs-function.md) + [ # Ceil ](ceil-function.md) + [ # Coalesce ](coalesce-function.md) + [ # Concat ](concat-function.md) + [ # contains ](contains-function.md) + [ # decimalToInt ](decimalToInt-function.md) + [ # dateDiff ](dateDiff-function.md) + [ # endsWith ](endsWith-function.md) + [ # epochDate ](epochDate-function.md) + [ # Exp ](exp-function.md) + [ # Extract ](extract-function.md) + [ # Floor ](floor-function.md) + [ # formatDate ](formatDate-function.md) + [ # Ifelse ](ifelse-function.md) + [ # in ](in-function.md) + [ # intToDecimal ](intToDecimal-function.md) + [ # isNotNull ](isNotNull-function.md) + [ # isNull ](isNull-function.md) + [ # isWorkDay ](isWorkDay-function.md) + [ # Left ](left-function.md) + [ # Locate ](locate-function.md) + [ # Log ](log-function.md) + [ # Ln ](ln-function.md) + [ # Ltrim ](ltrim-function.md) + [ # Mod ](mod-function.md) + [ # netWorkDays ](netWorkDays-function.md) + [ # Now ](now-function.md) + [ # notIn ](notIn-function.md) + [ # nullIf ](nullIf-function.md) + [ # parseDate ](parseDate-function.md) + [ # parseDecimal ](parseDecimal-function.md) + [ # parseInt ](parseInt-function.md) + [ # parseJson ](parseJson-function.md) + [ # Replace ](replace-function.md) + [ # Right ](right-function.md) + [ # Round ](round-function.md) + [ # Rtrim ](rtrim-function.md) + [ # Split ](split-function.md) + [ # Sqrt ](sqrt-function.md) + [ # startsWith ](startsWith-function.md) + [ # Strlen ](strlen-function.md) + [ # Substring ](substring-function.md) + [ # switch ](switch-function.md) + [ # toLower ](toLower-function.md) + [ # toString ](toString-function.md) + [ # toUpper ](toUpper-function.md) + [ # trim ](trim-function.md) + [ # truncDate ](truncDate-function.md) # addDateTime `addDateTime` adds or subtracts a unit of time from a datetime value. For example, `addDateTime(2,'YYYY',parseDate('02-JUL-2018', 'dd-MMM-yyyy') )` returns `02-JUL-2020`. You can use this function to perform date math on your date and time data. ## Syntax ``` addDateTime(amount, period, datetime) ``` ## Arguments *amount* A positive or negative integer value that represents the amount of time that you want to add or subtract from the provided datetime field. *period* A positive or negative value that represents the amount of time that you want to add or subtract from the provided datetime field. Valid periods are as follows: + YYYY: This returns the year portion of the date. + Q: This returns the quarter that the date belongs to (1–4). + MM: This returns the month portion of the date. + DD: This returns the day portion of the date. + WK: This returns the week portion of the date. The week starts on Sunday in Amazon Quick. + HH: This returns the hour portion of the date. + MI: This returns the minute portion of the date. + SS: This returns the second portion of the date. + MS: This returns the millisecond portion of the date. *datetime* The date or time that you want to perform date math on. ## Return type Datetime ## Example Let's say you have a field called `purchase_date` that has the following values. ``` 2018 May 13 13:24 2017 Jan 31 23:06 2016 Dec 28 06:45 ``` Using the following calculations, `addDateTime` modifies the values as shown following. ``` addDateTime(-2, 'YYYY', purchaseDate) 2016 May 13 13:24 2015 Jan 31 23:06 2014 Dec 28 06:45 addDateTime(4, 'DD', purchaseDate) 2018 May 17 13:24 2017 Feb 4 23:06 2017 Jan 1 06:45 addDateTime(20, 'MI', purchaseDate) 2018 May 13 13:44 2017 Jan 31 23:26 2016 Dec 28 07:05 ``` # addWorkDays `addWorkDays` Adds or subtracts a designated number of work days to a given date value. The function returns a date for a work day, that falls a designated work days after or before a given input date value. ## Syntax ``` addWorkDays(initDate, numWorkDays) ``` ## Arguments *initDate* A valid non-NULL date that acts as the start date for the calculation. + **Dataset field** – Any `date` field from the dataset that you are adding this function to. + **Date function** – Any date output from another `date` function, for example `parseDate`, `epochDate`, `addDateTime`., and so on. **Example** ``` addWorkDays(epochDate(1659484800), numWorkDays) ``` + **Calculated fields** – Any Quick calculated field that returns a `date` value. **Example** ``` calcFieldStartDate = addDateTime(10, “DD”, startDate) addWorkDays(calcFieldStartDate, numWorkDays) ``` + **Parameters** – Any Quick `datetime` parameter. **Example** ``` addWorkDays($paramStartDate, numWorkDays) ``` + Any combination of the above stated argument values. *numWorkDays* A non-NULL integer that acts as the end date for the calculation. + **Literal** – An integer literal directly typed in the expression editor. **Example** ``` ``` + **Dataset field** – Any date field from the dataset **Example** ``` ``` + **Scalar function or calculation** – Any scalar Quick function that returns an integer output from another, for example `decimalToInt`, `abs`, and so on. **Example** ``` addWorkDays(initDate, decimalToInt(sqrt (abs(numWorkDays)) ) ) ``` + **Calculated field** – Any Quick calculated field that returns a `date` value. **Example** ``` someOtherIntegerCalcField = (num_days * 2) + 12 addWorkDays(initDate, someOtherIntegerCalcField) ``` + **Parameter** – Any Quick `datetime` parameter. **Example** ``` addWorkDays(initDate, $param_numWorkDays) ``` + Any combination of the above stated argument values. ## Return type Integer ## Ouptut values Expected output values include: + Positive integer (when start\$1date < end\$1date) + Negative integer (when start\$1date > end\$1date) + NULL when one or both of the arguments get a null value from the `dataset field`. ## Input errors Disallowed argument values cause errors, as shown in the following examples. + Using a literal NULL as an argument in the expression is disallowed. **Example** ``` addWorkDays(NULL, numWorkDays) ``` **Example** ``` Error At least one of the arguments in this function does not have correct type. Correct the expression and choose Create again. ``` + Using a string literal as an argument, or any other data type other than a date, in the expression is disallowed. In the following example, the string **"2022-08-10"** looks like a date, but it is actually a string. To use it, you would have to use a function that converts to a date data type. **Example** ``` addWorkDays("2022-08-10", 10) ``` **Example** ``` Error Expression addWorkDays("2022-08-10", numWorkDays) for function addWorkDays has incorrect argument type addWorkDays(String, Number). Function syntax expects Date, Integer. ``` ## Example A positive integer as `numWorkDays` argument will yield a date in the future of the input date. A negative integer as `numWorkDays` argument will yield a resultant date in the past of the input date. A zero value for the `numWorkDays` argument yields the same value as input date whether or not it falls on a work day or a weekend. The `addWorkDays` function operates at the granularity: `DAY`. Accuracy cannot be preserved at any granularity which is lower or higher than `DAY` level. ``` addWorkDays(startDate, endDate) ``` Let’s assume there is a field named `employmentStartDate` with the following values: ``` 2022-08-10 2022-08-06 2022-08-07 ``` Using the above field and following calculations, `addWorkDays` returns the modified values as shown below: ``` addWorkDays(employmentStartDate, 7) 2022-08-19 2022-08-16 2022-08-16 addWorkDays(employmentStartDate, -5) 2022-08-02 2022-08-01 2022-08-03 addWorkDays(employmentStartDate, 0) 2022-08-10 2022-08-06 2022-08-07 ``` The following example calculates the total pro-rated bonus to be paid to each employee for 2 years based on how many days each employee has actually worked. ``` last_day_of_work = addWorkDays(employment_start_date, 730) total_days_worked = netWorkDays(employment_start_date, last_day_of_work) total_bonus = total_days_worked * bonus_per_day ``` ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/addWorkDays-function-example.png) # Abs `abs` returns the absolute value of a given expression. ## Syntax ``` abs(expression) ``` ## Arguments *expression* The expression must be numeric. It can be a field name, a literal value, or another function. # Ceil `ceil` rounds a decimal value to the next highest integer. For example, `ceil(29.02)` returns `30`. ## Syntax ``` ceil(decimal) ``` ## Arguments *decimal* A field that uses the decimal data type, a literal value like **17.62**, or a call to another function that outputs a decimal. ## Return type Integer ## Example The following example rounds a decimal field to the next highest integer. ``` ceil(salesAmount) ``` The following are the given field values. ``` 20.13 892.03 57.54 ``` For these field values, the following values are returned. ``` 21 893 58 ``` # Coalesce `coalesce` returns the value of the first argument that is not null. When a non-null value is found, the remaining arguments in the list are not evaluated. If all arguments are null, the result is null. 0-length strings are valid values and are not considered equivalent to null. ## Syntax ``` coalesce(expression1, expression2 [, expression3, ...]) ``` ## Arguments `coalesce` takes two or more expressions as arguments. All of the expressions must have the same data type or be able to be implicitly cast to the same data type. *expression* The expression can be numeric, datetime, or string. It can be a field name, a literal value, or another function. ## Return type `coalesce` returns a value of the same data type as the input arguments. ## Example The following example retrieves a customer's billing address if it exists, her street address if there is no billing address, or returns "No address listed" if neither address is available. ``` coalesce(billingAddress, streetAddress, 'No address listed') ``` # Concat Concat `concat` concatenates two or more strings. ## Syntax ``` concat(expression1, expression2 [, expression3 ...]) ``` ## Arguments `concat` takes two or more string expressions as arguments. *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. ## Return type String ## Examples The following example concatenates three string fields and adds appropriate spacing. ``` concat(salutation, ' ', firstName, ' ', lastName) ``` The following are the given field values. ``` salutation firstName lastName ------------------------------------------------------- Ms. Li Juan Dr. Ana Carolina Silva Mr. Nikhil Jayashankar ``` For these field values, the following values are returned. ``` Ms. Li Juan Dr. Ana Carolina Silva Mr. Nikhil Jayashankar ``` The following example concatenates two string literals. ``` concat('Hello', 'world') ``` The following value is returned. ``` Helloworld ``` # contains contains `contains` evaluates if the substring that you specify exists within an expression. If the expression contains the substring, contains returns true, and otherwise it returns false. ## Syntax ``` contains(expression, substring, string-comparison-mode) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. *substring* The set of characters to check against the *expression*. The substring can occur one or more times in the *expression*. *string-comparison-mode* (Optional) Specifies the string comparison mode to use: + `CASE_SENSITIVE` – String comparisons are case-sensitive. + `CASE_INSENSITIVE` – String comparisons are case-insensitive. This value defaults to `CASE_SENSITIVE` when blank. ## Return type Boolean ## Examples ### Default case sensitive example The following case sensitive example evaluates if `state_nm` contains **New**. ``` contains(state_nm, "New") ``` The following are the given field values. ``` New York new york ``` For these field values, the following values are returned. ``` true false ``` ### Case insensitive example The following case insensitive example evaluates if `state_nm` contains **new**. ``` contains(state_nm, "new", CASE_INSENSITIVE) ``` The following are the given field values. ``` New York new york ``` For these field values, the following values are returned. ``` true true ``` ### Example with conditional statements The contains function can be used as the conditional statement within the following If functions: [avgIf](https://docs.amazonaws.cn/quicksight/latest/user/avgIf-function.html), [minIf](https://docs.amazonaws.cn/quicksight/latest/user/minIf-function.html), [distinct\$1countIf](https://docs.amazonaws.cn/quicksight/latest/user/distinct_countIf-function.html), [countIf](https://docs.amazonaws.cn/quicksight/latest/user/countIf-function.html), [maxIf](https://docs.amazonaws.cn/quicksight/latest/user/maxIf-function.html), [medianIf](https://docs.amazonaws.cn/quicksight/latest/user/medianIf-function.html), [stdevIf](https://docs.amazonaws.cn/quicksight/latest/user/stdevIf-function.html), [stdevpIf](https://docs.amazonaws.cn/quicksight/latest/user/stdevpIf-function.html), [sumIf](https://docs.amazonaws.cn/quicksight/latest/user/sumIf-function.html), [varIf](https://docs.amazonaws.cn/quicksight/latest/user/varIf-function.html), and [varpIf](https://docs.amazonaws.cn/quicksight/latest/user/varpIf-function.html). The following example sums `Sales` only if `state_nm` contains **New**. ``` sumIf(Sales,contains(state_nm, "New")) ``` ### Does NOT contain example The conditional `NOT` operator can be used to evaluate if the expression does not contain the specified substring. ``` NOT(contains(state_nm, "New")) ``` ### Example using numeric values Numeric values can be used in the expression or substring arguments by applying the `toString` function. ``` contains(state_nm, toString(5) ) ``` # decimalToInt `decimalToInt` converts a decimal value to the integer data type by stripping off the decimal point and any numbers after it. `decimalToInt` does not round up. For example, `decimalToInt(29.99)` returns `29`. ## Syntax ``` decimalToInt(decimal) ``` ## Arguments *decimal* A field that uses the decimal data type, a literal value like **17.62**, or a call to another function that outputs a decimal. ## Return type Integer ## Example The following example converts a decimal field to an integer. ``` decimalToInt(salesAmount) ``` The following are the given field values. ``` 20.13 892.03 57.54 ``` For these field values, the following values are returned. ``` 20 892 57 ``` # dateDiff `dateDiff` returns the difference in days between two date fields. If you include a value for the period, `dateDiff` returns the difference in the period interval, rather than in days. ## Syntax ``` dateDiff(date1, date2,[period]) ``` ## Arguments `dateDiff` takes two dates as arguments. Specifying a period is optional. *date 1* The first date in the comparison. A date field or a call to another function that outputs a date. *date 2* The second date in the comparison. A date field or a call to another function that outputs a date. *period* The period of difference that you want returned, enclosed in quotes. Valid periods are as follows: + YYYY: This returns the year portion of the date. + Q: This returns the date of the first day of the quarter that the date belongs to. + MM: This returns the month portion of the date. + DD: This returns the day portion of the date. + WK: This returns the week portion of the date. The week starts on Sunday in Amazon Quick. + HH: This returns the hour portion of the date. + MI: This returns the minute portion of the date. + SS: This returns the second portion of the date. + MS: This returns the millisecond portion of the date. ## Return type Integer ## Example The following example returns the difference between two dates. ``` dateDiff(orderDate, shipDate, "MM") ``` The following are the given field values. ``` orderDate shipdate ============================= 01/01/18 03/05/18 09/13/17 10/20/17 ``` For these field values, the following values are returned. ``` 2 1 ``` # endsWith endsWith `endsWith` evaluates if the expression ends with a substring that you specify. If the expression ends with the substring, `endsWith` returns true, and otherwise it returns false. ## Syntax ``` endsWith(expression, substring, string-comparison-mode) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. *substring* The set of characters to check against the *expression*. The substring can occur one or more times in the *expression*. *string-comparison-mode* (Optional) Specifies the string comparison mode to use: + `CASE_SENSITIVE` – String comparisons are case-sensitive. + `CASE_INSENSITIVE` – String comparisons are case-insensitive. This value defaults to `CASE_SENSITIVE` when blank. ## Return type Boolean ## Examples ### Default case sensitive example The following case sensitive example evaluates if `state_nm` endsWith **"York"**. ``` endsWith(state_nm, "York") ``` The following are the given field values. ``` New York new york ``` For these field values, the following values are returned. ``` true false ``` ### Case insensitive example The following case insensitive example evaluates if `state_nm` endsWith **"york"**. ``` endsWith(state_nm, "york", CASE_INSENSITIVE) ``` The following are the given field values. ``` New York new york ``` For these field values, the following values are returned. ``` true true ``` ### Example with conditional statements The `endsWith` function can be used as the conditional statement within the following If functions: [avgIf](https://docs.amazonaws.cn/quicksight/latest/user/avgIf-function.html), [minIf](https://docs.amazonaws.cn/quicksight/latest/user/minIf-function.html), [distinct\$1countIf](https://docs.amazonaws.cn/quicksight/latest/user/distinct_countIf-function.html), [countIf](https://docs.amazonaws.cn/quicksight/latest/user/countIf-function.html), [maxIf](https://docs.amazonaws.cn/quicksight/latest/user/maxIf-function.html), [medianIf](https://docs.amazonaws.cn/quicksight/latest/user/medianIf-function.html), [stdevIf](https://docs.amazonaws.cn/quicksight/latest/user/stdevIf-function.html), [stdevpIf](https://docs.amazonaws.cn/quicksight/latest/user/stdevpIf-function.html), [sumIf](https://docs.amazonaws.cn/quicksight/latest/user/sumIf-function.html), [varIf](https://docs.amazonaws.cn/quicksight/latest/user/varIf-function.html), and [varpIf](https://docs.amazonaws.cn/quicksight/latest/user/varpIf-function.html). The following example sums `Sales` only if `state_nm` ends with **"York"**. ``` sumIf(Sales,endsWith(state_nm, "York")) ``` ### Does NOT contain example The conditional `NOT` operator can be used to evaluate if the expression does not start with the specified substring. ``` NOT(endsWith(state_nm, "York")) ``` ### Example using numeric values Numeric values can be used in the expression or substring arguments by applying the `toString` function. ``` endsWith(state_nm, toString(5) ) ``` # epochDate `epochDate` converts an epoch date into a standard date in the format yyyy-MM-dd**T**kk:mm:ss.SSS**Z**, using the format pattern syntax specified in [Class DateTimeFormat](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) in the Joda project documentation. An example is `2015-10-15T19:11:51.003Z`. `epochDate` is supported for use with analyses based on datasets stored in Quick (SPICE). ## Syntax ``` epochDate(epochdate) ``` ## Arguments *epochdate* An epoch date, which is an integer representation of a date as the number of seconds since 00:00:00 UTC on January 1, 1970. *epochdate* must be an integer. It can be the name of a field that uses the integer data type, a literal integer value, or a call to another function that outputs an integer. If the integer value is longer than 10 digits, the digits after the 10th place are discarded. ## Return type Date ## Example The following example converts an epoch date to a standard date. ``` epochDate(3100768000) ``` The following value is returned. ``` 2068-04-04T12:26:40.000Z ``` # Exp `exp` returns the base of natural log e raised to the power of a given expression. ## Syntax ``` exp(expression) ``` ## Arguments *expression* The expression must be numeric. It can be a field name, a literal value, or another function. # Extract `extract` returns a specified portion of a date value. Requesting a time-related portion of a date that doesn't contain time information returns 0. ## Syntax ``` extract(period, date) ``` ## Arguments *period* The period that you want extracted from the date value. Valid periods are as follows: + YYYY: This returns the year portion of the date. + Q: This returns the quarter that the date belongs to (1–4). + MM: This returns the month portion of the date. + DD: This returns the day portion of the date. + WD: This returns the day of the week as an integer, with Sunday as 1. + HH: This returns the hour portion of the date. + MI: This returns the minute portion of the date. + SS: This returns the second portion of the date. + MS: This returns the millisecond portion of the date. **Note** Extracting milliseconds is not supported in Presto databases below version 0.216. *date* A date field or a call to another function that outputs a date. ## Return type Integer ## Example The following example extracts the day from a date value. ``` extract('DD', orderDate) ``` The following are the given field values. ``` orderDate ========= 01/01/14 09/13/16 ``` For these field values, the following values are returned. ``` 01 13 ``` # Floor `floor` decrements a decimal value to the next lowest integer. For example, `floor(29.08)` returns `29`. ## Syntax ``` floor(decimal) ``` ## Arguments *decimal* A field that uses the decimal data type, a literal value like **17.62**, or a call to another function that outputs a decimal. ## Return type Integer ## Example The following example decrements a decimal field to the next lowest integer. ``` floor(salesAmount) ``` The following are the given field values. ``` 20.13 892.03 57.54 ``` For these field values, the following values are returned. ``` 20 892 57 ``` # formatDate `formatDate` formats a date using a pattern you specify. When you are preparing data, you can use `formatDate` to reformat the date. To reformat a date in an analysis, you choose the format option from the context menu on the date field. ## Syntax ``` formatDate(date, ['format']) ``` ## Arguments *date* A date field or a call to another function that outputs a date. *format* (Optional) A string containing the format pattern to apply. This argument accepts the format patterns specified in [Supported date formats](https://docs.amazonaws.cn/quicksight/latest/user/supported-date-formats.html). If you don't specify a format, this string defaults to yyyy-MM-dd**T**kk:mm:ss:SSS. ## Return type String ## Example The following example formats a UTC date. ``` formatDate(orderDate, 'dd-MMM-yyyy') ``` The following are the given field values. ``` order date ========= 2012-12-14T00:00:00.000Z 2013-12-29T00:00:00.000Z 2012-11-15T00:00:00.000Z ``` For these field values, the following values are returned. ``` 13 Dec 2012 28 Dec 2013 14 Nov 2012 ``` ## Example If the date contains single quotes or apostrophes, for example `yyyyMMdd'T'HHmmss`, you can handle this date format by using one of the following methods. + Enclose the entire date in double quotes, as shown in the following example: ``` formatDate({myDateField}, "yyyyMMdd'T'HHmmss") ``` + Escape the single quotes or apostrophes by adding a backslash ( `\` ) to the left of them, as shown in the following example: ``` formatDate({myDateField}, 'yyyyMMdd\'T\'HHmmss') ``` # Ifelse `ifelse` evaluates a set of *if*, *then* expression pairings, and returns the value of the *then* argument for the first *if* argument that evaluates to true. If none of the *if* arguments evaluate to true, then the value of the *else* argument is returned. ## Syntax ``` ifelse(if-expression-1, then-expression-1 [, if-expression-n, then-expression-n ...], else-expression) ``` ## Arguments `ifelse` requires one or more *if*,*then* expression pairings, and requires exactly one expression for the *else* argument. *if-expression* The expression to be evaluated as true or not. It can be a field name like **address1**, a literal value like **'Unknown'**, or another function like `toString(salesAmount)`. An example is `isNotNull(FieldName)`. If you use multiple AND and OR operators in the `if` argument, enclose statements in parentheses to identify processing order. For example, the following `if` argument returns records with a month of 1, 2, or 5 and a year of 2000. ``` ifelse((month = 5 OR month < 3) AND year = 2000, 'yes', 'no') ``` The next `if` argument uses the same operators, but returns records with a month of 5 and any year, or with a month of 1 or 2 and a year of 2000. ``` ifelse(month = 5 OR (month < 3 AND year = 2000), 'yes', 'no') ``` *then-expression* The expression to return if its *if* argument is evaluated as true. It can be a field name like **address1**, a literal value like **'Unknown'**, or a call to another function. The expression must have the same data type as the other `then` arguments and the `else` argument. *else-expression* The expression to return if none of the *if* arguments evaluate as true. It can be a field name like **address1**, a literal value like **'Unknown'**, or another function like `toString(salesAmount)`. The expression must have the same data type as all of the `then` arguments. ## Return type `ifelse` returns a value of the same data type as the values in *then-expression*. All data returned *then* and *else* expressions must be of the same data type or be converted to the same data type. ## Examples The following example generates a column of aliases for field `country`. ``` ifelse(country = "United States", "US", country = "China", "CN", country = "India", "IN", "Others") ``` For such use cases evaluating each value in a field against a list of literals, and returns the result corresponding to the first matching value., function switch is recommended to simplify your work. The previous example can be rewritten to the following statement using [https://docs.amazonaws.cn/quicksight/latest/user/switch-function.html](https://docs.amazonaws.cn/quicksight/latest/user/switch-function.html): ``` switch(country,"United States","US","China","CN","India","IN","Others") ``` The following example categorizes sales per customer into human-readable levels. ``` ifelse(salesPerCustomer < 1000, “VERY_LOW”, salesPerCustomer < 10000, “LOW”, salesPerCustomer < 100000, “MEDIUM”, “HIGH”) ``` The following example uses AND, OR, and NOT to compare multiple expressions using conditional operators to tag top customers NOT in Washington or Oregon with a special promotion, who made more than 10 orders. If no values are returned, the value `'n/a'` is used. ``` ifelse(( (NOT (State = 'WA' OR State = 'OR')) AND Orders > 10), 'Special Promotion XYZ', 'n/a') ``` The following examples use only OR to generate a new column that contains the name of continent that corresponds to each `country`. ``` ifelse(country = "United States" OR country = "Canada", "North America", country = "China" OR country = "India" OR country = "Japan", "Asia", "Others") ``` The previous example can be simplified as shown in the next example. The following example uses `ifelse` and [https://docs.amazonaws.cn/quicksight/latest/user/in-function.html](https://docs.amazonaws.cn/quicksight/latest/user/in-function.html) to create a value in a new column for any row where the tested value is in a literal list. You could use `ifelse` with [https://docs.amazonaws.cn/quicksight/latest/user/notIn-function.html](https://docs.amazonaws.cn/quicksight/latest/user/notIn-function.html) as well. ``` ifelse(in(country,["United States", "Canada"]), "North America", in(country,["China","Japan","India"]),"Asia","Others") ``` Authors are able to save a literal list in a multivalue parameter and use it in the [https://docs.amazonaws.cn/quicksight/latest/user/in-function.html](https://docs.amazonaws.cn/quicksight/latest/user/in-function.html) or [https://docs.amazonaws.cn/quicksight/latest/user/notIn-function.html](https://docs.amazonaws.cn/quicksight/latest/user/notIn-function.html) functions. The following example is an equivalent of the previous example, except that the literal lists are stored in two multivalue parameters. ``` ifelse(in(country,${NorthAmericaCountryParam}), "North America", in(country,${AsiaCountryParam}),"Asia", "Others") ``` The following example assigns a group to a sales record based on the sales total. The structure of each `if-then` phrase mimics the behavior of *between*, a keyword that doesn't currently work in calculated field expressions. For example, the result of the comparison `salesTotal >= 0 AND salesTotal < 500` returns the same values as the SQL comparison `salesTotal between 0 and 499`. ``` ifelse(salesTotal >= 0 AND salesTotal < 500, 'Group 1', salesTotal >= 500 AND salesTotal < 1000, 'Group 2', 'Group 3') ``` The following example tests for a NULL value by using `coalesce` to return the first non-NULL value. Instead of needing to remember the meaning of a NULL in a date field, you can use a readable description instead. If the disconnect date is NULL, the example returns the suspend date, unless both of those are NULL. Then `coalesce(DiscoDate, SuspendDate, '12/31/2491')` returns `'12/31/2491'`. The return value must match the other data types. This date might seem like an unusual value, but a date in the 25th century reasonably simulates the "end of time," defined as the highest date in a data mart. ``` ifelse ( (coalesce(DiscoDate, SuspendDate, '12/31/2491') = '12/31/2491'), 'Active subscriber', 'Inactive subscriber') ``` The following shows a more complex example in a more readable format, just to show that you don't need to compress your code all into one long line. This example provides for multiple comparisons of the value a survey result. It handles potential NULL values for this field and categorizes two acceptable ranges. It also labels one range that needs more testing and another that's not valid (out of range). For all remaining values, it applies the `else` condition, and labels the row as needing a retest three years after the date on that row. ``` ifelse ( isNull({SurveyResult}), 'Untested', {SurveyResult}=1, 'Range 1', {SurveyResult}=2, 'Range 2', {SurveyResult}=3, 'Need more testing', {SurveyResult}=99, 'Out of Range', concat ( 'Retest by ', toString ( addDateTime(3, "YYYY", {Date}) ) ) ) ``` The following example assigns a "manually" created region name to a group of states. It also uses spacing and comments, wrapped in `/* */`, to make it easier to maintain the code. ``` ifelse ( /* NE REGION*/ locate('New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire',{State}) > 0, 'Northeast', /* SE REGION*/ locate('Georgia, Alabama, South Carolina, Louisiana',{State}) > 0, 'Southeast', 'Other Region' ) ``` The logic for the region tagging breaks down as follows: 1. We list the states that we want for each region, enclosing each list in quotation marks to make each list a string, as follows: + `'New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire'` + `'Georgia, Alabama, South Carolina, Louisiana'` + You can add more sets, or use countries, cities, provinces, or What3Words if you want. 1. We ask if the value for `State` (for each row) is found in the list, by using the `locate` function to return a nonzero value if the state is found in the list, as follows. ``` locate('New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire',{State}) and locate('Georgia, Alabama, South Carolina, Louisiana',{State}) ``` 1. The `locate` function returns a number instead of a `TRUE` or `FALSE`, but `ifelse` requires the `TRUE`/`FALSE` Boolean value. To get around this, we can compare the result of `locate` to a number. If the state is in the list, the return value is greater than zero. 1. Ask if the state is present. ``` locate('New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire',{State}) > 0 ``` 1. If it's present the region, label it as the specific region, in this case a Northeast region. ``` /*The if expression:*/ locate('New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire',{State}) > 0, /*The then expression:*/ 'Northeast', ``` 1. Because we have states that aren't in a list, and because `ifelse` requires a single `else` expression, we provide `'Other Region'` as the label for the leftover states. ``` /*The if expression:*/ locate('New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire',{State}) > 0, /*The then expression:*/ 'Northeast', /*The else expression:*/ 'Other Region' ``` 1. We wrap all that in the `ifelse( )` function to get the final version. The following example leaves out the Southeast region states that were in the original. You can add them back in place of the *``* tag. If you want to add more regions, you can construct more copies of those two lines and alter the list of states to suit your purpose. You can change the region name to something that suits you, and change the field name from `State` to anything that you need. ``` ifelse ( /*The if expression:*/ locate('New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire',{State}) > 0, /*The then expression:*/ 'Northeast', /**/ /*The else expression:*/ 'Other Region' ) ``` **Note** There are other ways to do the initial comparison for the if expression. For example, suppose that you pose the question "What states are not missing from this list?" rather than "Which states are on the list?" If you do, you might phrase it differently. You might compare the locate statement to zero to find values that are missing from the list, and then use the NOT operator to classify them as "not missing," as follows. ``` /*The if expression:*/ NOT (locate('New York, New Jersey, Connecticut, Vermont, Maine, Rhode Island, New Hampshire',{State}) = 0), ``` Both versions are correct. The version that you choose should make the most sense to you and your team, so you can maintain it easily. If all the options seem equal, choose the simplest. # in in `in` evaluates if an expression exists within a literal list. If the list contains the expression, in returns true, and otherwise it returns false. `in` is case sensitive for string type inputs. `in` accepts two kinds of literal list, one is manually entered list and the other is a [multivalue parameter](https://docs.amazonaws.cn/quicksight/latest/user/parameters-in-quicksight.html). ## Syntax Using a manually entered list: ``` in(expression, [literal-1, ...]) ``` Using a multivalue parameter: ``` in(expression, $multivalue_parameter) ``` ## Arguments *expression* The expression to be compared with the elements in literal list. It can be a field name like `address`, a literal value like ‘ **Unknown**’, a single value parameter, or a call to another scalar function—provided this function is not an aggregate function or a table calculation. *literal list* (required) This can be a manually entered list or a multivalue parameter. This argument accepts up to 5,000 elements. However, in a direct query to a third party data source, for example Oracle or Teradata, the restriction can be smaller. + ***manually entered list*** – One or more literal values in a list to be compared with the expression. The list should be enclosed in square brackets. All the literals to compare must have the same datatype as the expression. + ***multivalue parameter*** – A pre-defined multivalue parameter passed in as a literal list. The multivalue parameter must have the same datatype as the expression. ## Return type Boolean: TRUE/FALSE ## Example with a static list The following example evaluates the `origin_state_name` field for values in a list of string. When comparing string type input, `in` only supports case sensitive comparison. ``` in(origin_state_name,["Georgia", "Ohio", "Texas"]) ``` The following are the given field values. ``` "Washington" "ohio" "Texas" ``` For these field values the following values are returned. ``` false false true ``` The third return value is true because only "Texas" is one of the included values. The following example evaluates the `fl_date` field for values in a list of string. In order to match the type, `toString` is used to cast the date type to string type. ``` in(toString(fl_date),["2015-05-14","2015-05-15","2015-05-16"]) ``` ![\[An image of the results of the function example, shown in table form.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/in-function-example-manual-list.png) Literals and NULL values are supported in expression argument to be compared with the literals in list. Both of the following two examples will generate a new column of TRUE values. ``` in("Washington",["Washington","Ohio"]) ``` ``` in(NULL,[NULL,"Ohio"]) ``` ## Example with mutivalue parameter Let's say an author creates a [multivalue parameter](https://docs.amazonaws.cn/quicksight/latest/user/parameters-in-quicksight.html) that contains a list of all the state names. Then the author adds a control to allow the reader to select values from the list. Next, the reader selects three values—"Georgia", "Ohio", and "Texas"—from the parameter's drop down list control. In this case, the following expression is equivalent to the first example, where those three state names are passed as the literal list to be compared with the `original_state_name` field. ``` in (origin_state_name, ${stateName MultivalueParameter}) ``` ## Example with `ifelse` `in` can be nested in other functions as a boolean value. One example is that authors can evaluate any expression in a list and return the value they want by using `in` and `ifelse`. The following example evaluates if the `dest_state_name` of a flight is in a particular list of US states and returns different categories of the states based on the comparison. ``` ifelse(in(dest_state_name,["Washington", "Oregon","California"]), "WestCoastUSState", "Other US State") ``` ![\[An image of the results of the function example, shown in table form.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/in-function-with-ifelse.png) # intToDecimal `intToDecimal` converts an integer value to the decimal data type. ## Syntax ``` intToDecimal(integer) ``` ## Arguments *int* A field that uses the integer data type, a literal value like **14**, or a call to another function that outputs an integer. ## Return type Decimal(Fixed) in the legacy data preparation experience. Decimal(Float) in the new data preparation experience. ## Example The following example converts an integer field to a decimal. ``` intToDecimal(price) ``` The following are the given field values. ``` 20 892 57 ``` For these field values, the following values are returned. ``` 20.0 892.0 58.0 ``` You can apply formatting inside an analysis, for example to format `price` as currency. # isNotNull `isNotNull` evaluates an expression to see if it is not null. If the expression is not null, `isNotNull` returns true, and otherwise it returns false. ## Syntax ``` isNotNull(expression) ``` ## Arguments *expression* The expression to be evaluated as null or not. It can be a field name like **address1** or a call to another function that outputs a string. ## Return type Boolean ## Example The following example evaluates the sales\$1amount field for null values. ``` isNotNull(salesAmount) ``` The following are the given field values. ``` 20.13 (null) 57.54 ``` For these field values, the following values are returned. ``` true false true ``` # isNull `isNull` evaluates an expression to see if it is null. If the expression is null, `isNull` returns true, and otherwise it returns false. ## Syntax ``` isNull(expression) ``` ## Arguments *expression* The expression to be evaluated as null or not. It can be a field name like **address1** or a call to another function that outputs a string. ## Return type Boolean ## Example The following example evaluates the sales\$1amount field for null values. ``` isNull(salesAmount) ``` The following are the given field values. ``` 20.13 (null) 57.54 ``` For these field values, the following values are returned. ``` false true false ``` The following example tests for a NULL value in an `ifelse` statement, and returns a human-readable value instead. ``` ifelse( isNull({ActiveFlag}) , 'Inactive', 'Active') ``` # isWorkDay `isWorkDay` evaluates a given date-time value to determine if the value is a workday or not. `isWorkDay` assumes a standard 5-day work week starting from Monday and ending on Friday. Saturday and Sunday are assumed to be weekends. The function always calculates its result at the `DAY` granularity and is exclusive of the given input date. ## Syntax ``` isWorkDay(inputDate) ``` ## Arguments *inputDate* The date-time value that you want to evaluate. Valid values are as follows: + Dataset fields: Any `date` field from the dataset that you are adding this function to. + Date Functions: Any date output from another `date` function, for example, `parseDate`. + Calculated fields: Any Quick calculated field that returns a `date` value. + Parameters: Any Quick `DateTime` parameter. ## Return type Integer (`0` or `1`) ## Example The following exaple determines whether or not the `application_date` field is a work day. Let's assume that there's a field named `application_date` with the following values: ``` 2022-08-10 2022-08-06 2022-08-07 ``` When you use these fields and add the following calculations, `isWorkDay` returns the below values: ``` isWorkDay({application_date}) 1 0 0 ``` The following example filters employees whose employment ends on a work day and determines whether their employment began on work day or a weekend using conditional formatting: ``` is_start_date_work_day = isWorkDay(employment_start_date) is_end_date_work_day = isWorkDay(employment_end_date) ``` ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/isWorkDay-example.png) # Left Left `left` returns the leftmost characters from a string, including spaces. You specify the number of characters to be returned. ## Syntax ``` left(expression, limit) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. *limit* The number of characters to be returned from *expression*, starting from the first character in the string. ## Return type String ## Example The following example returns the first 3 characters from a string. ``` left('Seattle Store #14', 3) ``` The following value is returned. ``` Sea ``` # Locate Locate `locate` locates a substring that you specify within another string, and returns the number of characters until the first character in the substring. The function returns 0 if it doesn't find the substring. The function is 1-based. ## Syntax ``` locate(expression, substring, start) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. *substring* The set of characters in *expression* that you want to locate. The substring can occur one or more times in *expression*. *start* (Optional) If *substring* occurs more than once, use *start* to identify where in the string the function should start looking for the substring. For example, suppose that you want to find the second example of a substring and you think it typically occurs after the first 10 characters. You specify a *start* value of 10. It should start from 1. ## Return type Integer ## Examples The following example returns information about where the first occurrence of the substring 'and' appears in a string. ``` locate('1 and 2 and 3 and 4', 'and') ``` The following value is returned. ``` 3 ``` The following example returns information about where the first occurrence of the substring 'and' appears in a string after the fourth character. ``` locate('1 and 2 and 3 and 4', 'and', 4) ``` The following value is returned. ``` 9 ``` # Log `log` returns the base 10 logarithm of a given expression. ## Syntax ``` log(expression) ``` ## Arguments *expression* The expression must be numeric. It can be a field name, a literal value, or another function. # Ln `ln` returns the natural logarithm of a given expression. ## Syntax ``` ln(expression) ``` ## Arguments *expression* The expression must be numeric. It can be a field name, a literal value, or another function. # Ltrim Ltrim `ltrim` removes preceding blank space from a string. ## Syntax ``` ltrim(expression) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. ## Return type String ## Example The following example removes the preceding spaces from a string. ``` ltrim(' Seattle Store #14') ``` The following value is returned. ``` Seattle Store #14 ``` # Mod Use the `mod` function to find the remainder after dividing the number by the divisor. You can use the `mod` function or the modulo operator (%) interchangeably. ## Syntax ``` mod(number, divisor) ``` ``` number%divisor ``` ## Arguments *number* The number is the positive integer that you want to divide and find the remainder for. *divisor* The divisor is the positive integer that you are dividing by. If the divisor is zero, this function returns an error on dividing by 0. ## Example The following examples return the modulo of 17 when dividing by 6. The first example uses the % operator, and the second example uses the mod function. ``` 17%6 ``` ``` mod( 17, 6 ) ``` The following value is returned. ``` 5 ``` # netWorkDays `netWorkDays` returns the number of working days between the provided two date fields or even custom date values generated using other Quick date functions such as `parseDate` or `epochDate` as an integer. `netWorkDays` assumes a standard 5-day work week starting from Monday and ending on Friday. Saturday and Sunday are assumed to be weekends. The calculation is inclusive of both `startDate` and `endDate`. The function operates on and shows results for DAY granularity. ## Syntax ``` netWorkDays(startDate, endDate) ``` ## Arguments *startDate* A valid non-NULL date that acts as the start date for the calculation. + Dataset fields: Any `date` field from the dataset that you are adding this function to. + Date Functions: Any date output from another `date` function, for example, `parseDate`. + Calculated fields: Any Quick calculated field that returns a `date` value. + Parameters: Any Quick `DateTime` parameter. + Any combination of the above stated argument values. *endDate* A valid non-NULL date that acts as the end date for the calculation. + Dataset fields: Any `date` field from the dataset that you are adding this function to. + Date Functions: Any date output from another `date` function, for example, `parseDate`. + Calculated fields: Any Quick calculated field that returns a `date` value. + Parameters: Any Quick `DateTime` parameter. + Any combination of the above stated argument values. ## Return type Integer ## Ouptut values Expected output values include: + Positive integer (when start\$1date < end\$1date) + Negative integer (when start\$1date > end\$1date) + NULL when one or both of the arguments get a null value from the `dataset field`. ## Example The following example returns the number of work days falling between two dates. Let's assume that there's a field named `application_date` with the following values: ``` netWorkDays({startDate}, {endDate}) ``` The following are the given field values. ``` startDate endDate netWorkDays 9/4/2022 9/11/2022 5 9/9/2022 9/2/2022 -6 9/10/2022 9/11/2022 0 9/12/2022 9/12/2022 1 ``` The following example calculates the number of days worked by each employee and the salary expended per day for each employee: ``` days_worked = netWorkDays({employment_start_date}, {employment_end_date}) salary_per_day = {salary}/{days_worked} ``` The following example filters employees whose employment ends on a work day and determines whether their employment began on work day or a weekend using conditional formatting: ``` is_start_date_work_day = netWorkDays(employment_start_date) is_end_date_work_day = netWorkDays(employment_end_date) ``` ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/netWorkDays-function-example.png) # Now For database datasets that directly query the database, `now` returns the current date and time using the settings and format specified by the database server. For SPICE and Salesforce data sets, `now` returns the UTC date and time, in the format `yyyy-MM-ddTkk:mm:ss:SSSZ` (for example, 2015-10-15T19:11:51:003Z). ## Syntax ``` now() ``` ## Return type Date # notIn notIn `notIn` evaluates if an expression exists within a literal list. If the list doesn’t contain the expression, `notIn` returns true, and otherwise it returns false. `notIn` is case sensitive for string type inputs. `notIn` accepts two kinds of literal list, one is manually entered list and the other is a [multivalue parameter](https://docs.amazonaws.cn/quicksight/latest/user/parameters-in-quicksight.html). ## Syntax Using a manually entered list: ``` notIn(expression, [literal-1, ...]) ``` Using a multivalue parameter: ``` notIn(expression, $multivalue_parameter) ``` ## Arguments *expression* The expression to be compared with the elements in literal list. It can be a field name like `address`, a literal value like ‘ **Unknown**’, a single value parameter, or a call to another scalar function—provided this function is not an aggregate function or a table calculation. *literal list* (required) This can be a manually entered list or a multivalue parameter. This argument accepts up to 5,000 elements. However, in a direct query to a third party data source, for example Oracle or Teradata, the restriction can be smaller. + ***manually entered list*** – One or more literal values in a list to be compared with the expression. The list should be enclosed in square brackets. All the literals to compare must have the same datatype as the expression. + ***multivalue parameter*** – A pre-defined multivalue parameter passed in as a literal list. The multivalue parameter must have the same datatype as the expression. ## Return type Boolean: TRUE/FALSE ## Example with a manually entered list The following example evaluates the `origin_state_name` field for values in a list of string. When comparing string type input, `notIn` only supports case sensitive comparison. ``` notIn(origin_state_name,["Georgia", "Ohio", "Texas"]) ``` The following are the given field values. ``` "Washington" "ohio" "Texas" ``` For these field values the following values are returned. ``` true true false ``` The third return value is false because only "Texas" is one of the excluded values. The following example evaluates the `fl_date` field for values in a list of string. In order to match the type, `toString` is used to cast the date type to string type. ``` notIn(toString(fl_date),["2015-05-14","2015-05-15","2015-05-16"]) ``` ![\[An image of the results of the function example, shown in table form.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/notin-function-example-manual-list.png) Literals and NULL values are supported in expression argument to be compared with the literals in list. Both of the following two examples will generate a new column of FALSE values. ``` notIn("Washington",["Washington","Ohio"]) ``` ``` notIn(NULL,[NULL,"Ohio"]) ``` ## Example with mutivalue parameter Let's say an author creates a [multivalue parameter](https://docs.amazonaws.cn/quicksight/latest/user/parameters-in-quicksight.html) that contains a list of all the state names. Then the author adds a control to allow the reader to select values from the list. Next, the reader selects three values—"Georgia", "Ohio", and "Texas"—from the parameter's drop down list control. In this case, the following expression is equivalent to the first example, where those three state names are passed as the literal list to be compared with the `original_state_name` field. ``` notIn (origin_state_name, ${stateName MultivalueParameter}) ``` ## Example with `ifelse` `notIn` can be nested in other functions as a boolean value. One example is that authors can evaluate any expression in a list and return the value they want by using `notIn` and `ifelse`. The following example evaluates if the `dest_state_name` of a flight is in a particular list of US states and returns different categories of the states based on the comparison. ``` ifelse(notIn(dest_state_name,["Washington", "Oregon","California"]), "notWestCoastUSState", "WestCoastUSState") ``` ![\[An image of the results of the function example, shown in table form.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/notin-function-with-ifelse.png) # nullIf `nullIf` compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression. ## Syntax ``` nullIf(expression1, expression2) ``` ## Arguments `nullIf` takes two expressions as arguments. *expression* The expression can be numeric, datetime, or string. It can be a field name, a literal value, or another function. ## Return type String ## Example The following example returns nulls if the reason for a shipment delay is unknown. ``` nullIf(delayReason, 'unknown') ``` The following are the given field values. ``` delayReason ============ unknown back ordered weather delay ``` For these field values, the following values are returned. ``` (null) back ordered weather delay ``` # parseDate parseDate `parseDate` parses a string to determine if it contains a date value, and returns a standard date in the format `yyyy-MM-ddTkk:mm:ss.SSSZ` (using the format pattern syntax specified in [Class DateTimeFormat](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) in the Joda project documentation), for example 2015-10-15T19:11:51.003Z. This function returns all rows that contain a date in a valid format and skips any rows that don't, including rows that contain null values. Quick supports dates in the range from Jan 1, 1900 00:00:00 UTC to Dec 31, 2037 23:59:59 UTC. For more information, see [Supported date formats](https://docs.amazonaws.cn/quicksight/latest/user/supported-date-formats.html). ## Syntax ``` parseDate(expression, ['format']) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'1/1/2016'**, or a call to another function that outputs a string. *format* (Optional) A string containing the format pattern that *date\$1string* must match. For example, if you are using a field with data like **01/03/2016**, you specify the format 'MM/dd/yyyy'. If you don't specify a format, it defaults to `yyyy-MM-dd`. Rows whose data doesn't conform to *format* are skipped. Different date formats are supported based on the type of dataset used. Use the following table to see details of supported date formats. **** [\[See the AWS documentation website for more details\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/parseDate-function.html) ## Return type Date ## Example The following example evaluates `prodDate` to determine if it contains date values. ``` parseDate(prodDate, 'MM/dd/yyyy') ``` The following are the given field values. ``` prodDate -------- 01-01-1999 12/31/2006 1/18/1982 7/4/2010 ``` For these field values, the following rows are returned. ``` 12-31-2006T00:00:00.000Z 01-18-1982T00:00:00.000Z 07-04-2010T00:00:00.000Z ``` # parseDecimal parseDecimal `parseDecimal` parses a string to determine if it contains a decimal value. This function returns all rows that contain a decimal, integer, or null value, and skips any rows that don't. If the row contains an integer value, it is returned as a decimal with up to 4 decimal places. For example, a value of '2' is returned as '2.0'. ## Syntax ``` parseDecimal(expression) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'9.62'**, or a call to another function that outputs a string. ## Return type Decimal(Fixed) in the legacy data preparation experience. Decimal(Float) in the new data preparation experience. ## Example The following example evaluates `fee` to determine if it contains decimal values. ``` parseDecimal(fee) ``` The following are the given field values. ``` fee -------- 2 2a 12.13 3b 3.9 (null) 198.353398 ``` For these field values, the following rows are returned. ``` 2.0 12.13 3.9 (null) 198.3533 ``` # parseInt parseInt `parseInt` parses a string to determine if it contains an integer value. This function returns all rows that contain a decimal, integer, or null value, and skips any rows that don't. If the row contains a decimal value, it is returned as the nearest integer, rounded down. For example, a value of '2.99' is returned as '2'. ## Syntax ``` parseInt(expression) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'3'**, or a call to another function that outputs a string. ## Return type Integer ## Example The following example evaluates `feeType` to determine if it contains integer values. ``` parseInt(feeType) ``` The following are the given field values. ``` feeType -------- 2 2.1 2a 3 3b (null) 5 ``` For these field values, the following rows are returned. ``` 2 2 3 (null) 5 ``` # parseJson parseJson Use `parseJson` to extract values from a JSON object. If your dataset is stored in Quick SPICE, you can use `parseJson` when you are preparing a data set, but not in calculated fields during analysis. For direct query, you can use `parseJson` both during data preparation and analysis. The `parseJson` function applies to either strings or to JSON native data types, depending on the dialect, as shown in the following table. | Dialect | Type | | --- | --- | | PostgreSQL | JSON | | Amazon Redshift | String | | Microsoft SQL Server | String | | MySQL | JSON | | Teradata | JSON | | Oracle | String | | Presto | String | | Snowflake | Semistructured data type object and array | | Hive | String | ## Syntax ``` parseJson(fieldName, path) ``` ## Arguments *fieldName* The field containing the JSON object that you want to parse. *path* The path to the data element you want to parse from the JSON object. Only letters, numbers, and blank spaces are supported in the path argument. Valid path syntax includes: + *\$1* – Root object + *.* – Child operator + *[ ]* – Subscript operator for array ## Return type String ## Example The following example evaluates incoming JSON to retrieve a value for item quantity. By using this during data preparation, you can create a table out of the JSON. ``` parseJson({jsonField}, “$.items.qty”) ``` The following shows the JSON. ``` { "customer": "John Doe", "items": { "product": "Beer", "qty": 6 }, "list1": [ "val1", "val2" ], "list2": [ { "list21key1": "list1value1" } ] } ``` For this example, the following value is returned. ``` 6 ``` ## Example The following example evaluates `JSONObject1` to extract the first key value pair (KVP), labeled `"State"`, and assign the value to the calculated field that you are creating. ``` parseJson(JSONObject1, “$.state”) ``` The following are the given field values. ``` JSONObject1 ----------- {"State":"New York","Product":"Produce","Date Sold":"1/16/2018","Sales Amount":"$3423.39"} {"State":"North Carolina","Product":"Bakery Products","Date Sold":"2/1/2018","Sales Amount":"$3226.42"} {"State":"Utah","Product":"Water","Date Sold":"4/24/2018","Sales Amount":"$7001.52"} ``` For these field values, the following rows are returned. ``` New York North Carolina Utah ``` # Replace Replace `replace` replaces part of a string with another string that you specify. ## Syntax ``` replace(expression, substring, replacement) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. *substring* The set of characters in *expression* that you want to replace. The substring can occur one or more times in *expression*. *replacement* The string you want to have substituted for *substring*. ## Return type String ## Example The following example replaces the substring 'and' with 'or'. ``` replace('1 and 2 and 3', 'and', 'or') ``` The following string is returned. ``` 1 or 2 or 3 ``` # Right Right `right` returns the rightmost characters from a string, including spaces. You specify the number of characters to be returned. ## Syntax ``` right(expression, limit) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. *limit* The number of characters to be returned from *expression*, starting from the last character in the string. ## Return type String ## Example The following example returns the last five characters from a string. ``` right('Seattle Store#14', 12) ``` The following value is returned. ``` tle Store#14 ``` # Round `round` rounds a decimal value to the closest integer if no scale is specified, or to the closest decimal place if scale is specified. ## Syntax ``` round(decimal, scale) ``` ## Arguments *decimal* A field that uses the decimal data type, a literal value like **17.62**, or a call to another function that outputs a decimal. *scale* The number of decimal places to use for the return values. ## Return type | Operand | Return type in the legacy data preparation experience | Return type in the new data preparation experience | | --- | --- | --- | | INT | DECIMAL(FIXED) | DECIMAL(FIXED) | | DECIMAL(FIXED) | DECIMAL(FIXED) | DECIMAL(FIXED) | | DECIMAL(FLOAT) | DECIMAL(FIXED) | DECIMAL(FLOAT) | ## Example The following example rounds a decimal field to the closest second decimal place. ``` round(salesAmount, 2) ``` The following are the given field values. ``` 20.1307 892.0388 57.5447 ``` For these field values, the following values are returned. ``` 20.13 892.04 58.54 ``` # Rtrim Rtrim `rtrim` removes following blank space from a string. ## Syntax ``` rtrim(expression) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. ## Return type String ## Example The following example removes the following spaces from a string. ``` rtrim('Seattle Store #14 ') ``` For these field values, the following values are returned. ``` Seattle Store #14 ``` # Split Split `split` splits a string into an array of substrings, based on a delimiter that you choose, and returns the item specified by the position. You can only add `split` to a calculated field during data preparation, not to an analysis. This function is not supported in direct queries to Microsoft SQL Server. ## Syntax ``` split(expression, delimiter , position) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street;1402 35th Ave;1818 Elm Ct;11 Janes Lane'**, or a call to another function that outputs a string. *delimiter* The character that delimits where the string is broken into substrings. For example, `split('one|two|three', '|', 2)` becomes the following. ``` one two three ``` If you choose `position = 2`, `split` returns `'two'`. *position* (Required) The position of the item to return from the array. The position of the first item in the array is 1. ## Return type String array ## Example The following example splits a string into an array, using the semicolon character (;) as the delimiter, and returns the third element of the array. ``` split('123 Test St;1402 35th Ave;1818 Elm Ct;11 Janes Lane', ';', 3) ``` The following item is returned. ``` 1818 Elm Ct ``` This function skips items containing null values or empty strings. # Sqrt `sqrt` returns the square root of a given expression. ## Syntax ``` sqrt(expression) ``` ## Arguments *expression* The expression must be numeric. It can be a field name, a literal value, or another function. # startsWith startsWith `startsWith` evaluates if the expression starts with a substring that you specify. If the expression starts with the substring, `startsWith` returns true, and otherwise it returns false. ## Syntax ``` startsWith(expression, substring, string-comparison-mode) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. *substring* The set of characters to check against the *expression*. The substring can occur one or more times in the *expression*. *string-comparison-mode* (Optional) Specifies the string comparison mode to use: + `CASE_SENSITIVE` – String comparisons are case-sensitive. + `CASE_INSENSITIVE` – String comparisons are case-insensitive. This value defaults to `CASE_SENSITIVE` when blank. ## Return type Boolean ## Examples ### Default case sensitive example The following case sensitive example evaluates if `state_nm` startsWith **New**. ``` startsWith(state_nm, "New") ``` The following are the given field values. ``` New York new york ``` For these field values, the following values are returned. ``` true false ``` ### Case insensitive example The following case insensitive example evaluates if `state_nm` startsWith **new**. ``` startsWith(state_nm, "new", CASE_INSENSITIVE) ``` The following are the given field values. ``` New York new york ``` For these field values, the following values are returned. ``` true true ``` ### Example with conditional statements The `startsWith` function can be used as the conditional statement within the following If functions: [avgIf](https://docs.amazonaws.cn/quicksight/latest/user/avgIf-function.html), [minIf](https://docs.amazonaws.cn/quicksight/latest/user/minIf-function.html), [distinct\$1countIf](https://docs.amazonaws.cn/quicksight/latest/user/distinct_countIf-function.html), [countIf](https://docs.amazonaws.cn/quicksight/latest/user/countIf-function.html), [maxIf](https://docs.amazonaws.cn/quicksight/latest/user/maxIf-function.html), [medianIf](https://docs.amazonaws.cn/quicksight/latest/user/medianIf-function.html), [stdevIf](https://docs.amazonaws.cn/quicksight/latest/user/stdevIf-function.html), [stdevpIf](https://docs.amazonaws.cn/quicksight/latest/user/stdevpIf-function.html), [sumIf](https://docs.amazonaws.cn/quicksight/latest/user/sumIf-function.html), [varIf](https://docs.amazonaws.cn/quicksight/latest/user/varIf-function.html), and [varpIf](https://docs.amazonaws.cn/quicksight/latest/user/varpIf-function.html). The following example sums `Sales` only if state\$1nm starts with **New**. ``` sumIf(Sales,startsWith(state_nm, "New")) ``` ### Does NOT contain example The conditional `NOT` operator can be used to evaluate if the expression does not start with the specified substring. ``` NOT(startsWith(state_nm, "New")) ``` ### Example using numeric values Numeric values can be used in the expression or substring arguments by applying the `toString` function. ``` startsWith(state_nm, toString(5) ) ``` # Strlen Strlen `strlen` returns the number of characters in a string, including spaces. ## Syntax ``` strlen(expression) ``` ## Arguments *expression* An expression can be the name of a field that uses the string data type like **address1**, a literal value like **'Unknown'**, or another function like `substring(field_name,0,5)`. ## Return type Integer ## Example The following example returns the length of the specified string. ``` strlen('1421 Main Street') ``` The following value is returned. ``` 16 ``` # Substring Substring `substring` returns the characters in a string, starting at the location specified by the *start* argument and proceeding for the number of characters specified by the *length* arguments. ## Syntax ``` substring(expression, start, length) ``` ## Arguments *expression* An expression can be the name of a field that uses the string data type like **address1**, a literal value like **'Unknown'**, or another function like `substring(field_name,1,5)`. *start* The character location to start from. *start* is inclusive, so the character at the starting position is the first character in the returned value. The minimum value for *start* is 1. *length* The number of additional characters to include after *start*. *length* is inclusive of *start*, so the last character returned is (*length* - 1) after the starting character. ## Return type String ## Example The following example returns the 13th through 19th characters in a string. The beginning of the string is index 1, so you begin counting at the first character. ``` substring('Fantasy and Science Fiction',13,7) ``` The following value is returned. ``` Science ``` # switch `switch` compares a *condition-expression* with the literal labels, within a set of literal label and *return-expression* pairings. It then returns the *return-expression* corresponding to the first literal label that's equal to the *condition-expression*. If no label equals to the *condition-expression*, `switch` returns the *default-expression*. Every *return-expression* and *default-expression* must have the same datatype. ## Syntax ``` switch(condition-expression, label-1, return-expression-1 [, label-n, return-expression-n ...], default-expression) ``` ## Arguments `switch` requires one or more *if*,*then* expression pairings, and requires exactly one expression for the *else* argument. *condition-expression* The expression to be compared with the label-literals. It can be a field name like `address`, a literal value like '`Unknown`', or another scalar function like `toString(salesAmount)`. *label* The literal to be compared with the *condition-expression* argument, all of the literals must have the same data type as *condition-expression* argument. `switch` accepts up to 5000 labels. *return-expression* The expression to return if the value of its label equals to the value of the *condition-expression*. It can be a field name like `address`, a literal value like '`Unknown`', or another scalar function like `toString(salesAmount)`. All of the *return-expression* arguments must have the same data type as the *default-expression*. *default-expression* The expression to return if no value of any label arguments equals to the value of *condition-expression*. It can be a field name like `address`, a literal value like '`Unknown`', or another scalar function like `toString(salesAmount)`. The *default-expression* must have the same data type as all of the *return-expression* arguments. ## Return type `switch` returns a value of the same data type as the values in *return-expression*. All data returned *return-expression* and *default-expression* must be of the same data type or be converted to the same data type. ## General Examples The following example returns the Amazon Web Services Region code of input region name. ``` switch(region_name, "US East (N. Virginia)", "us-east-1", "Europe (Ireland)", "eu-west-1", "US West (N. California)", "us-west-1", "other regions") ``` The following are the given field values. ``` "US East (N. Virginia)" "US West (N. California)" "Asia Pacific (Tokyo)" ``` For these field values the following values are returned. ``` "us-east-1" "us-west-1" "other regions" ``` ## Use switch to replace `ifelse` The following `ifelse` use case is an equivalent of the previous example, for `ifelse` evaluating whether values of one field equals to different literal values, using `switch` instead is a better choice. ``` ifelse(region_name = "US East (N. Virginia)", "us-east-1", region_name = "Europe (Ireland)", "eu-west-1", region_name = "US West (N. California)", "us-west-1", "other regions") ``` ## Expression as return value The following example uses expressions in *return-expressions*: ``` switch({origin_city_name}, "Albany, NY", {arr_delay} + 20, "Alexandria, LA", {arr_delay} - 10, "New York, NY", {arr_delay} * 2, {arr_delay}) ``` The preceding example changes the expected delay time for each flight from a particular city. ![\[An image of the results of the function example, shown in table form.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/switch-function-example.png) # toLower toLower `toLower` formats a string in all lowercase. `toLower` skips rows containing null values. ## Syntax ``` toLower(expression) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. ## Return type String ## Example The following example converts a string value into lowercase. ``` toLower('Seattle Store #14') ``` The following value is returned. ``` seattle store #14 ``` # toString toString `toString` formats the input expression as a string. `toString` skips rows containing null values. ## Syntax ``` toString(expression) ``` ## Arguments *expression* An expression can be a field of any data type, a literal value like **14.62**, or a call to another function that returns any data type. ## Return type String ## Example The following example returns the values from `payDate` (which uses the `date` data type) as strings. ``` toString(payDate) ``` The following are the given field values. ``` payDate -------- 1992-11-14T00:00:00.000Z 2012-10-12T00:00:00.000Z 1973-04-08T00:00:00.000Z ``` For these field values, the following rows are returned. ``` 1992-11-14T00:00:00.000Z 2012-10-12T00:00:00.000Z 1973-04-08T00:00:00.000Z ``` # toUpper toUpper `toUpper` formats a string in all uppercase. `toUpper` skips rows containing null values. ## Syntax ``` toUpper(expression) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. ## Return type String ## Example The following example converts a string value into uppercase. ``` toUpper('Seattle Store #14') ``` The following value is returned. ``` SEATTLE STORE #14 ``` # trim trim `trim` removes both preceding and following blank space from a string. ## Syntax ``` trim(expression) ``` ## Arguments *expression* The expression must be a string. It can be the name of a field that uses the string data type, a literal value like **'12 Main Street'**, or a call to another function that outputs a string. ## Return type String ## Example The following example removes the following spaces from a string. ``` trim(' Seattle Store #14 ') ``` For these field values, the following values are returned. ``` Seattle Store #14 ``` # truncDate `truncDate` returns a date value that represents a specified portion of a date. For example, requesting the year portion of the value 2012-09-02T00:00:00.000Z returns 2012-01-01T00:00:00.000Z. Specifying a time-related period for a date that doesn't contain time information returns the initial date value unchanged. ## Syntax ``` truncDate('period', date) ``` ## Arguments *period* The period of the date that you want returned. Valid periods are as follows: + YYYY: This returns the year portion of the date. + Q: This returns the date of the first day of the quarter that the date belongs to. + MM: This returns the month portion of the date. + DD: This returns the day portion of the date. + WK: This returns the week portion of the date. The week starts on Sunday in Amazon Quick. + HH: This returns the hour portion of the date. + MI: This returns the minute portion of the date. + SS: This returns the second portion of the date. + MS: This returns the millisecond portion of the date. *date* A date field or a call to another function that outputs a date. ## Return type Date ## Example The following example returns a date representing the month of the order date. ``` truncDate('MM', orderDate) ``` The following are the given field values. ``` orderDate ========= 2012-12-14T00:00:00.000Z 2013-12-29T00:00:00.000Z 2012-11-15T00:00:00.000Z ``` For these field values, the following values are returned. ``` 2012-12-01T00:00:00.000Z 2013-12-01T00:00:00.000Z 2012-11-01T00:00:00.000Z ```