.svg){kind=logo}
Patterns
- 15 May 2024
- 18 Minutes to read
- Print
- DarkLight
Patterns
- Updated on 15 May 2024
- 18 Minutes to read
- Print
- DarkLight
Article Summary
Share feedback
Thanks for sharing your feedback!
The table below provides a catalog of derived field, restriction, and transformation patterns available in Ursa Studio.
Derived fields are akin to a SQL select clause that includes a calculation or transformation. Derived fields enable users to create a new field, which is typically based on the contents of another field, e.g., Concatenate or Case When. These patterns can be used when developing objects and measures and when analyzing measures in Analytics Portal. Derived field patterns can be chained together to create complex, multi-step calculations and transformations. When chaining patterns together, the ordering of patterns dictates the order in which the logic will be applied.
Restrictions are akin to a SQL where clause. Restrictions can be used when developing objects and measures. The table also includes Upstack Restrictions which are used when linking (joining) one object to another. The table’s Analogous SQL column contains SQL commands and logic representing the pattern’s basic functionality.
Transformations can be used when developing objects and measures.
The table’s Analogous SQL column contains SQL commands and logic representing the pattern’s basic functionality. While most of the patterns have more complex, flexible logic, developers will understand the pattern’s primary function if they are familiar with the SQL.
| Category | Pattern Name | Analogous SQL | Pattern Description |
| Derived Field | Absolute Value | ABS() | Generates the absolute value of an integer or float input. |
| Derived Field | Add Decimal Point to ICD-9 or ICD-10 Diagnosis Code | - | Generates a text value representing a given ICD-9 or ICD-10 diagnosis code with a decimal point included when appropriate. |
| Derived Field | Add Decimal Point to ICD-9 Procedure Code | - | Generates a text value representing a given ICD-9 procedure code with a decimal point included when appropriate. |
| Derived Field | Age Restriction | Ex. SELECT DATEDIFF(YEAR, date1, dob) >= 65 | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating an individual’s age in years as of a given reference date is above or below a target age. |
| Derived Field | All Are True | Ex. field1 = 1 AND field2 = 1 AND field3 = 1 | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating whether an arbitrary number of numeric fields all take the value 1 (“true”). |
| Derived Field | Any Are True | Ex. field1 = 1 OR field2 = 1 | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating whether at least one of an arbitrary number of numeric fields takes the value 1 (“true”). |
| Derived Field | Arithmetic | +, -, *, / | Generates a numeric value representing the result of a simple (operand-operator-operand) arithmetic calculation; the following operators may be used: +, -, *, /. |
| Derived Field | Case When | CASE | Generates a value based on the evaluation of an ordered sequence of logical “case” statements, each with its own result value; all results must be of the same data type. |
| Derived Field | Coalesce | COALESCE(); ISNULL(); IFNULL(); NVL() | Generates a value by evaluating an ordered list of fields and returning the value of the first field taking a non-NULL value; all fields must be of the same data type. |
| Derived Field | Concatenate | CONCAT() | Generates a text value representing the combination of an ordered sequence of text fields and/or text literals. |
| Derived Field | Convert Date to Text | CAST() | Casts a date field to a text data type according to a supplied date format. |
| Derived Field | Convert Integer to Float | CAST() | Casts an integer to a float data type. |
| Derived Field | Convert Number to Text | CAST() | Casts a number to a text data type. |
| Derived Field | Convert Text Duration to Float | - | Casts a text field representing a time duration into a possibly-fractional number of seconds/minutes/etc. |
| Derived Field | Convert Text to Date | CAST() | Casts a text field as a date according to a supplied date format. |
| Derived Field | Convert Text to Datetime | CAST() | Casts a text field as a datetime according to a supplied date format. |
| Derived Field | Convert Text to Integer | CAST() | Casts a text field as an integer if it's correctly formatted, NULL otherwise. |
| Derived Field | Convert Text to Lower Case | LOWER() | Converts a text field to lowercase. |
| Derived Field | Convert Text to Number | CAST() | Casts a text field as a number if it's correctly formatted, NULL otherwise. |
| Derived Field | Convert Text to Upper Case | UPPER() | Converts a text field to uppercase. |
| Derived Field | Count of Instances | Ex. COUNT(1) OVER (PARTITION BY partition_field) | Generates a numeric value representing the count of instances sharing the same partition. |
| Derived Field | Cumulative Sum Across Instances | SUM() over (... range between unbounded preceding and current row) | Generates the numeric value representing the running sum of values for a given numeric field from a partition of records with the same values as the current record in a number of given fields; the sum is calculated by ordering records within the partition and summing values from the current record and all prior records. |
| Derived Field | Data End Date | Ex. TO_DATE('2010-01-01', 'YYYY-MM-DD') | Generates a date value representing the data end date in the Global Parameters table (ursa.mi_aa_001). |
| Derived Field | Data Start Date | Ex. TO_DATE('2020-01-01', 'YYYY-MM-DD') | Generates a date value representing the data start date in the Global Parameters table (ursa.mi_aa_001). |
| Derived Field | Date Offset | DATEADD(), DATE_ADD() | Generates a date value representing the calendar date a given number of days, weeks, months, or years before or after a given reference date. |
| Derived Field | Days Since Last Instance | Ex. EXTRACT(DAY FROM (date_field1 - LAG(date_field1) OVER (PARTITION BY partition_field ORDER BY sort_field))) | Generates a positive numeric value representing the number of days elapsed between the current instance and the most recent prior instance – defined by a given date field – among instances sharing the same partition. The pattern returns NULL if no prior instance can be found, and a field may be supplied to break ties between instances with the same date. |
| Derived Field | Days Until Next Instance | Ex. (EXTRACT(DAY FROM (date_field1 - LEAD(date_field1) OVER (PARTITION BY partition_field ORDER BY sort_field)))) * -1 | Generates a positive numeric value representing the number of days elapsed between the current instance and the next instance – defined by a given date field – among instances sharing the same partition. The pattern returns NULL if no prior instance can be found, and a field may be supplied to break ties between instances with the same date. |
| Derived Field | Difference As Relative Change | Ex. (field2 - field1)/field1 | Generates a numeric value representing the difference between a base value and a comparison value divided by the base value. |
| Derived Field | Distance Between Coordinate Points | - | Generates the distance in miles between two coordinate points based on their latitude and longitude |
| Derived Field | Earliest Date Within Instance | LEAST() | Generates a date value representing the earliest date – defined by a given date field – among a set of instances in the current scope that hold the same values as the current instance in a number of given fields. |
| Derived Field | Elapsed Time | DATEDIFF(), DATE_DIFF() | Generates a numeric value representing the amount of time elapsed between two given date fields. Time may be generated in units of minutes, hours, days, weeks, months, years, 100s of years, and 1000s of years. |
| Derived Field | Equality Filter | = | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating whether a given field takes the same value as another given field or specified value. |
| Derived Field | Extract Slot from Delimited Field | SPLIT_PART() | Extracts the 1-indexed nth value from a delimited field. Returns NULL if the delimited field does not have a value in that slot. |
| Derived Field | Extract Substring | SUBSTRING(), SUBSTR() | Generates a text value representing a continuous sequence of characters from a given text field. |
| Derived Field | Extract Value from JSON | JSON_EXTRACT_PATH_TEXT() | Generates a text value corresponding to the value of the key:value pair found at the specified path location within a referenced JSON document string. Returns an empty string if the specified location does not exist in the JSON document. |
| Derived Field | First Date in Period | DATE_TRUNC() | Generates a date value representing the first calendar date within a specified period containing a given reference date. |
| Derived Field | First Value Across Instances | Ex. FIRST_VALUE(field1) OVER (PARTITION BY partition_field ORDER BY sort_field ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) | Generates the value in the target field associated with the first record from within a designated partition of records after ordering as specified. |
| Derived Field | Generate Composite Key | CONCAT() | Generates a unique identifier by concatenating a set of fields. |
| Derived Field | Greatest Value Within Instance | GREATEST() | Generates a numeric value representing the largest value from a series of given numeric fields. |
| Derived Field | Inequality Filter | !=, <, <=, >, >= | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating whether a given field takes a value with a certain non-equality relationship (!=, <, <=, >, >=) as another given field or value. |
| Derived Field | Instantiation Datetime | CURRENT_TIMESTAMP() | Generates a value representing the date and time the current report is run or the current object is ELTed. |
| Derived Field | Is In Report-Designated Control Values | - | Applies a restriction with parameters that are set at the report-level, allowing the measure logic to differ from report to report. Generates a neumeric value of 1 (“true”) if that criterion is met and 0 (“false”) otherwise. |
| Derived Field | Is In Value Set | IN (‘Value’, ‘Value’, ‘Value’, ‘Value’) | Evaluates whether a given text field is a member of a given value set; if invoked as a restriction, keeps only instances meeting that criterion; if invoked as a derived field, generates a numeric value of 1 ("true") if that criterion is met and 0 ("false") otherwise. |
| Derived Field | Is Not Null | IS NOT NULL | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating whether a given field is not NULL. |
| Derived Field | Is Null | IS NULL | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating whether a given field is NULL. |
| Derived Field | Is Overlapping Interval | - | Evaluates whether one interval overlaps with a second interval for any amount of time. (Two intervals are not considered overlapping if one ends on the exact date or datetime that the other starts; an instantaneous interval — i.e., one with identical start and end datetimes — can be considered overlapping if it falls in the interior of the other interval.) |
| Derived Field | Is Patient Affiliation Active | - | Evaluates whether a given patient affiliation is active for the patient with the given patient ID as of a given reference date; if invoked as a derived field, generates a numeric value of 1 ("true") if that criterion is met and 0 ("false") otherwise." |
| Derived Field | Is Provider Affiliation Active | - | Evaluates whether a given provider affiliation is active for the provider with the given provider ID as of a given reference date; if invoked as a derived field, generates a numeric value of 1 ("true") if that criterion is met and 0 ("false") otherwise. |
| Derived Field | Latest Date Within Instance | GREATEST() | Generates a date value representing the most advanced date value from a series of given date fields. |
| Derived Field | Least Value Within Instance | LEAST() | Generates a numeric value representing the smallest value from a series of given numeric fields. |
| Derived Field | Leftmost Characters | LEFT() | Generates a text value representing the leftmost characters in a reference text field. |
| Derived Field | Leftmost Characters up to Target | Ex. LEFT(field1, POSITION('A' IN field1) - 1) | Generates a text value representing the leftmost characters up to a target character or substring in a reference text field. |
| Derived Field | Length of String | LEN(), LENGTH() | Generates the length of a string. |
| Derived Field | Literal | Ex. SELECT CAST(‘Literal’ AS VARCHAR(25)) | Generates a given value in the form of a given data type. |
| Derived Field | Location of Character in String | POSITION() | Identifies the first one-indexed position of a substring within a string, returning 0 if the substring is not found. |
| Derived Field | Look Up Value Set Using Cluster | - | For a given value set cluster and reference text value, look up which value set in the cluster contains the reference value and generate an appropriate text output value. |
| Derived Field | Lookup Table Value | - | Generates a text value representing the value associated with the given key in the selected lookup table. |
| Derived Field | Master Patient ID | - | Uses the "Matching of Source Local Patient ID to Ursa Patient ID" table to look up the master patient ID for the location patient ID given a source. |
| Derived Field | Maximum Value Across Instances | Ex. MAX(field1) OVER (PARTITION BY partition_field) | Generates a numeric or date value representing the largest or latest value of a field among a shared partition of instances. |
| Derived Field | Minimum Value Across Instances | Ex. MIN(field1) OVER (PARTITION BY partition_field) | Generates a numeric or date value representing the smallest or earliest value of a field among a shared partition of instances. |
| Derived Field | Name of Active Patient Affiliation | - | Generates a text value representing the name of the active patient affiliation for a patient with the given patient ID as of a given reference date from among the affiliations associated with the given cluster. |
| Derived Field | Name of Active Provider Affiliation | - | Generates a text value representing the name of the active provider affiliation for a provider with the given provider ID as of a given reference date from among the affiliations associated with the given cluster. |
| Derived Field | Name of Active Value Set | - | Generates a text value representing the name of the active value set containing a given text value from among the value sets associated with the given cluster. |
| Derived Field | Next or Previous Value | Ex. LEAD(field1, 1) OVER (PARTITION BY partition_field ORDER BY sort_field) | Generates the value of a field, among a shared partition of instances, that is found a user-specified number of instances ahead or behind the current instance and ordered by a given field. |
| Derived Field | Null | CAST() | Generates the value NULL cast as a particular database type. |
| Derived Field | Ordinal Rank Amongst Instances | Ex. ROW_NUMBER() OVER (PARTITION BY partition_field ORDER BY sort_field) | Generates a numeric value representing the ordinal rank (e.g., 1st, 2nd, 3rd, etc.) of the current instance – defined by a given sequence of ordering fields – among a shared partition. |
| Derived Field | Pad Left | LPAD() | Ensures a minimum length field by adding characters to the left of the original number or string. This pattern will never truncate the original number or string even if it is longer than the specified length. |
| Derived Field | Pad Right | RPAD() | Ensures a minimum length field by adding characters to the right of the original number or string. This pattern will never truncate the original number or string even if it is longer than the specified length. |
| Derived Field | Part of Date | DATEPART(); DATE_PART() | Generates a numeric value representing one of the following parts of a given date: minute of the hour, minute of the day, hour of the day, day of the week, day of the month, month of the year, year. |
| Derived Field | Random Number | RANDOM() | Generates a random float or int between a specified range. |
| Derived Field | Random Rank | RANDOM() | Assigns a random ordinal rank to each row. |
| Derived Field | Rank as Percentile Among Instances | Ex. 100.0 * PERCENT_RANK() OVER (PARTITION BY partition_field ORDER BY sort_field) | Generates a numeric value representing the percentile rank between 0 and 100 of the current instance – defined by a given sequence of ordering field – among a set of instances in the current scope that hold the same values as the current instance in a number of given fields. |
| Derived Field | Remove Leftmost Characters | SUBSTRING() | Generates a text value representing all but the leftmost characters in a reference text field. |
| Derived Field | Remove Rightmost Characters | SUBSTRING() | Generates a text value representing all but the rightmost characters in a reference text field. |
| Derived Field | Remove Special Characters | REPLACE() | Generates a text value representing a given text value from which all characters of a given type (hyphen, single quote, double quote, or period) have been removed. |
| Derived Field | Rightmost Characters | RIGHT() | Generates a text value representing the rightmost characters in a reference text field. |
| Derived Field | Round Number | ROUND() | Rounds a number to a specified number of decimal places. |
| Derived Field | Round Number to Nearest Integer | ROUND() | Rounds a number to the nearest integer. |
| Derived Field | Sum Across Instances | Ex. SUM(field1) OVER (PARTITION BY partition_field) | Generates a numeric value representing the sum of values for given numeric field among a shared partition of instances. |
| Derived Field | Sum Within Instance | Ex. field1 + field2 + field3 + field4… | Generates a numeric value representing the sum of values for a given set of numeric fields present on the current instance. |
| Derived Field | Switch | (simplified) CASE WHEN | Generates a value based on the evaluation of an ordered sequence of possible values against the same field, each with its own result; all results must be of the same data type. |
| Derived Field | Text Contains Statement | LIKE ‘% %’ | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating whether a given text field contains a given text value. |
| Derived Field | Text Section Contains Statement | Ex. SUBSTRING(field1, 3, 5) LIKE ‘% %’ | Generates a numeric value of 1 (“true”) or 0 (“false”) indicating whether a given section of a given text field contains a given text value. |
| Derived Field | Trim | LTRIM(RTRIM()) | Trims the whitespace from a string. |
| Derived Field | Truncate Date | DATE_TRUNC() | Generates a date value representing one of the following truncations of a given datetime field: date from a datetime; first date of a calendar month; first date of a calendar quarter; first date of a calendar year. |
| Derived Field | Truncate Number to Integer | TRUNC() | Truncates a number to integer. |
| Derived Field | Value of Field | Ex. SELECT field1 AS ‘field name’ | Generates a value currently taken by a given field. |
| Restriction | Age Restriction | Ex. WHERE date_field1 - date_of_birth_field >= 64 * 365.25 | Evaluates whether an individual's age in years as of a given reference date is above or below a target age; if invoked as a restriction, keeps only instances meeting the age criterion. |
| Restriction | All Are True | Ex. WHERE field1 = 1 AND field2 = 1 | Evaluates whether an arbitrary number of numeric fields all take the value 1 ("true"); if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Any Are True | Ex. WHERE field1 = 1 OR field2 = 1 | Evaluates whether at least one of an arbitrary number of numeric fields takes the value 1 ("true"); if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Equality Filter | Ex. WHERE field1 = ‘value’ | Evaluates whether a given field takes the same value as another given field; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Foreign Key | Ex. ON t1.id = t2.id | Keeps only instances for which a field on the current instance takes the same value as a given field on a different object. |
| Restriction | Inequality Filter | Ex. WHERE field1 != 1 | Evaluates whether a given field takes a value with a certain non-equality relationship (!=, <, <=, >, >=) as another given field; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Is In Report-Designated Control Values | - | Applies a restriction with parameters that are set at the report level, allowing the measure logic to differ from report to report. If the report specifies no control values then none will be applied. |
| Restriction | Is In Value Set | Ex. WHERE field1 IN (‘Value1’, ‘Value2’, ‘Value3’) | Evaluates whether a given text field is a member of a given value set; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Is Not In Value Set | Ex. WHERE field1 NOT IN (‘Value1’, ‘Value2’, ‘Value3’) | Evaluates whether a given text field is absent from a given value set; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Is Not Null | Ex. WHERE field1 IS NOT NULL | Evaluates whether a given field is not NULL; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Is Null | Ex. WHERE field1 IS NULL | Evaluates whether a given field is NULL; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Is Patient Affiliation Active | - | Evaluates whether a given patient affiliation is active for the patient with the given patient ID as of a given reference date; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Is Patient Affiliation Inactive | - | Evaluates whether a given patient affiliation is not active for the patient with the given patient ID as of a given reference date; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Is Provider Affiliation Active | - | Evaluates whether a given provider affiliation is active for the provider with the given provider ID as of a given reference date; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Is Provider Affiliation Inactive | - | Evaluates whether a given provider affiliation is not active for the provider with the given provider ID as of a given reference date; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Keep Closest Downstack Object | - | Keeps only the first instance of the downstack object occurring either immediately prior to or immediately subsequent to the current upstack instance – based on a given downstack object date and a given upstack instance date – from among a set of downstack instances that share the same values in a number of given fields; a field may be supplied to break ties between instances with the same date. This pattern is for use in defining upstack or numerator-denominator relationships only. |
| Restriction | Keep If Days Distance | - | Keeps the current instance if the number of days elapsed between the current instance and the most recent prior instance or subsequent instance – defined by a given date field – among a set of instances in the current scope that hold the same values as the current instance in a number of given fields – meets an equality or inequality criterion; a field may be supplied to break ties between instances with the same date. |
| Restriction | Keep In Interval | Ex. WHERE reference_date_field >= interval_start_date_field AND reference_date_field < period_end_date_field | Keeps the current instance if a given reference date falls within a given date interval; a reference date equal to the start date of an interval is considered to fall within the interval, while a reference date equal to the end date of an interval is considered to not fall within the interval. |
| Restriction | Keep Interval if Overlapping | - | Keeps the current interval instance if it overlaps with a second comparison interval for any amount of time. (Two intervals are not considered overlapping if one ends on the exact date or datetime that the other starts; an instantaneous interval — i.e., one with identical start and end datetimes — can be considered overlapping if it falls in the interior of the other interval.) |
| Restriction | Keep Nth Following Reference Date | - | Keeps the current instance if it is a given ordinal rank (e.g., 1st, 2nd, 3rd, etc.) in sequence following a reference date – defined by a given date field – among a set of instances in the current scope that hold the same values as the current instance in a number of given fields; a field may be supplied to break ties between instances with the same date. |
| Restriction | Keep Nth Preceding Reference Date | - | Keeps the current instance if it is a given ordinal rank (e.g., 1st, 2nd, 3rd, etc.) in sequence prior to a reference date – defined by a given date field – among a set of instances in the current scope that hold the same values as the current instance in a number of given fields; a field may be supplied to break ties between instances with the same date. |
| Restriction | Keep Ordinal N | Ex. SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY partition_field ORDER BY sort_field DESC NULLS LAST) AS ranked_partition_field) t1 WHERE ranked_partition_field = 1 | Keeps the current instance if it is a given ordinal rank (e.g., 1st, 2nd, 3rd, etc.) in sequence – defined by a given ordering field – among a set of instances in the current scope that hold the same values as the current instance in a number of given fields; a field may be supplied to break ties between instances with the same value on the ordering field. |
| Restriction | Keep Ordinals M Through N | Ex. SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY partition_field ORDER BY sort_field DESC NULLS LAST) AS ranked_partition_field) t1 WHERE ranked_partition_field >= 1 AND ranked_partition_field <= 10 | Keeps the current instance if it has an ordinal rank (e.g., 1st, 2nd, 3rd, etc.) falling within the parameter bounds, inclusive — defined by a given ordering — among the set of instances that hold the same values as the current instance in one or more given fields. |
| Restriction | Text Contains Statement | Ex. WHERE field1 LIKE ‘%value%’ | Evaluates whether a given text field contains a given text value; if invoked as a restriction, keeps only instances meeting that criterion. |
| Restriction | Text Section Contains Statement | Ex. WHERE SUBSTRING(field1, 3, 5) LIKE ‘%value%’ | Evaluates whether a given section of a given text field contains a given text value; if invoked as a restriction, keeps only instances meeting that criterion. |
| Transformation | Reshape Wide to Long | - | Transforms wide-form data into long-form data. |
| Transformation | Start New Scope | - | Wraps all the preceding derived fields, restrictions, and transformations into a subquery. |
Was this article helpful?