﻿ Transform Templates - Numeric

# Transform Templates - Numeric

This topic discusses application of the Transform Dialog with Tables on a numeric field in a table. See the Transform Dialog and Transform Templates topics for use and for links to lists of templates for other data types for tables and use of the Transform Dialog with other components, such as Drawings and Images.

### Tables, Numeric Types

 Absolute Value Take the absolute value of the number in the source field and put it into the target field.  Absolute value leaves positive numbers unchanged and converts negative numbers into their positive equivalents.     Example: Put in Temp the Absolute Value of Numbers. Add Add the two source fields and put the result in the target field.   Example: Add the Numbers and Fractions fields and put in Temp. Arc Cosine Take the arc cosine (inverse cosine) of the source field and put it into the target field.   Example: Put in Temp the Arc Cosine of Small. Arc Sine Take the arc sine (inverse sine) of the source field and put it into the target field.   Example: Put in Temp the Arc Sine of Small. Arc Tangent Take the arc tangent (inverse tangent) of the source field and put it into the target field.   Example: Put in Temp the Arc Tangent of Numbers. Arc Tangent of Ratio Take the arc tangent (inverse tangent) of the ratio between the DY field and the DX field and put into the target field.   Example: Put in Temp the Arc Tangent of the Ratio between Numbers as the DY field and Fractions as the DX field. Area Given a geom that contains an area object, place the area in square units of measure of that area object into the target field. Base 10 Logarithm Put the base 10 logarithm (also called the common logarithm) of the source field into the target field.   Example: Put in Temp the Base 10 Logarithm of Numbers. Base 2 Logarithm Put the base 2 logarithm (also called the binary logarithm) of the source field into the target field.   Example: Put in Temp the Base 2 Logarithm of Numbers. Bessel J0 A Bessel function of the first kind: Put the result of the Bessel function J0(x) using the source field as x into the target.   Example: Put in Temp the Bessel J0 function of Numbers. Bessel J1 A Bessel function of the first kind: Put the result of the Bessel function J1(x) using the source field as x into the target.   Example: Put in Temp the Bessel J1 function of Numbers. Bessel Jn The nth Bessel function of the first kind: Put the result of the Bessel function Jn(x) for Order n using the source field as x into the target.  Specifying 2 for Order uses a J2(x) Bessel function.  Using 0 or 1 for the Order is equivalent to using J0(x) or J1(x), respectively.   Example: Put in Temp the  Bessel Jn function for Order 2 of Numbers. Bessel Y0 A Bessel function of the second kind: Put the result of the Bessel Function Y0(x) using the source field as x into the target.   Example: Put in Temp the Bessel Y0 function of Numbers. Bessel Y1 A Bessel function of the second kind: Put the result of the Bessel Function Y1(x) using the source field as x into the target.   Example: Put in Temp the Bessel Y1 function of Numbers. Bessel Yn The nth Bessel function of the second kind: Put the result of the Bessel Function Yn(x) for Order n using the source field as x into the target.  Specifying 2 for Order uses a Y2(x) Bessel function.  Using 0 or 1 for the Order is equivalent to using Y0(x) or Y1(x), respectively.   Example: Put in Temp the  Bessel Yn function for Order 2 of Numbers. Binary And Perform bitwise AND operation on the source fields and puts the result into the target field.   Bitwise operators make no sense if we do not know the numeric type of the field, in these examples all unsigned 8 bit integers of uint8 type, and how those are represented as binary numbers.   Example: Put in Temp_Uint the Binary And result using Uint_A and Uint_B. Because the binary function operates on each bit in the uint8 types in the table, only when all eight bits of the value match does the AND provide a non-zero result for each bit.  In this case that happens only in the second record. Binary Not Performs bitwise NOT operation on the source fields and puts the result into the target field. This is a binary inversion: all 0’s become 1’s and all 1’s become 0’s.   Example: Put in Temp_Uint the Binary Not result using Uint_A. Inverting all binary bits for the uint8 value 0 would result in 255.  Inverting each representation as a binary in effect counts down by 1 in the result as Uint_A counts up by 1. Binary Or Performs bitwise OR operation on the source fields and puts the result into the target field.   Example: Put in Temp_Uint the Binary Or result using Uint_A and Uint_B. The binary representation of 2 in a uint8 match exactly for all bits so the result is 2 as well.  For all cases where the value of Uint_B is 0 the result is simply whatever is in Uint_A.  In the one case where the two fields are different, the fourth record, the OR operation results in a bitwise addition, adding the single binary "on" bit for the value of 1 to the binary pattern for 4 to get a result of 5. Binary Xor Performs bitwise XOR operation on the source fields and puts the result into the target field.   Example: Put in Temp_Uint the Binary Xor result using Uint_A and Uint_B. The Boolean XOR function generates a FALSE when both inputs are TRUE.   The result in a bitwise comparison is a FALSE, or 0 when all bits in both source fields are identical.  That only happens in our example in the second record where all of the bits are the same when the two values are both the same, 2, thus creating a result of 0.  In all of the other cases where the two values are different the result is similar to that of the Binary Or operator. Ceiling Put the rounded up integer value of the source field into the target field.   Example: Put in Temp the Ceiling value of Fractions. Ceiling up to Decimals Put the value of the source field rounded up to the specified number of Decimal positions into the target field.   Example: Put in Temp the Ceiling up to Decimals value of Fractions with Decimals set to 2. Compare Order Compare the source field to the comparison field and put a 1, 0, or -1 into the target field, with 1 if the source is greater than the comparison, 0 if the  same and -1 if the source is less than the comparison.   Example: Compare Order with Numbers the source and Fractions the comparison field.  Some values are greater, some less. Example: Compare Order with Numbers the source and Numbers the comparison field. All values compare the same since it is the same field. Complementary Error Function Take the complementary error function (also called erfc(z) ) of the source field and put it into the target field.   Example: Put in Temp the Complementary Error Function of Numbers. Copy Copy the source field into the target field.   Example: Copy Numbers into Temp. Cosine Take the cosine of the source field and put it into the target field.   Example: Put in Temp the Cosine of Numbers. Cube Root Take the cube root of the source field and put it into the target field.   Example: Put in Temp the Cube Root of Numbers. Date Day Copy the day part of a datetime source field as a number into the target field.   Example: Put in Temp the Date Day part of Calendar. Date Hour Copy the hour part of a datetime source field as a number into the target field.   Example: Put in Temp the Date Hour part of Calendar. Date Millisecond Copy the millisecond part of a datetime source field as a number into the target field.   Example: Put in Temp the Date Millisecond part of Calendar. Date Minute Copy the minute part of a datetime source field as a number into the target field.   Example: Put in Temp the Date Minute part of Calendar. Date Month Copy the month part of a datetime source field as a number into the target field.   Example: Put in Temp the Date Month part of Calendar. Date Second Copy the seconds part of a datetime source field as a number into the target field.   Example: Put in Temp the Date Second part of Calendar. Date Year Copy the year part of a datetime source field as a number into the target field.   Example: Put in Temp the Date Year part of Calendar. Distance Given two geom fields, geom 1 and geom 2, return the closest distance in drawing units of measure between the objects in geom 1 and the object in geom 2.     For example, if in a table that provides a list of cities and geom 1 contains a point at the center of the city while geom 2 contains a point at the center of the airport which serves that city, distance would for each record report the distance between the city and its airport.  Distance computes Cartesian distance. Divide Divide the source field by the divide field and put the result into the target field.   Example: Divide the Numbers field by Series and put the result in Temp. Divide and Truncate Divide the source field by the divide field and put the integer part of the result into the target field.   Example: Divide and Truncate the Numbers field by Series and put the result in Temp. Error Function Take the error function (also called erf(z) ) of the source field and put it into the target field.   Example: Put in Temp the Error Function of Numbers. Exponent The exponential function: Take e to the power of the source field and place result into the target field. For anything but small numbers in the source field such exponentiation very rapidly results in numbers larger than can be represented in most types and thus stores a NULL in the target.     Example: Put in Temp the Exponent of Series. Floor Put the rounded down integer value of the source field into the target field.   Example: Put in Temp the Floor value of Fractions. Floor down to Decimals Put the value of the source field rounded up to the specified number of Decimal positions into the target field.   Example: Put in Temp the Floor down to Decimals value of Fractions with Decimals set to 2. Fractional Part Take only the decimal fraction portion of the source and put into the target field.   Example: Put in Temp the Fractional Part of Fractions. Fractional Part from Decimals Take only the decimal fraction portion of the source beyond the specified number of Decimal positions and put into the target field.   Example: Put in Temp the Fractional Part from Decimals of Fractions with Decimals set to 2. Gamma Function Put the gamma function for the source field into the target field.   Example: Put in Temp the Gamma Function of Series. Hyperbolic Arc Cosine Put the hyperbolic arc cosine of the source field into the target field.   Example: Put in Temp the Hyperbolic Arc Cosine of Series. Hyperbolic Arc Sine Put the hyperbolic arc sine of the source field into the target field.   Example: Put in Temp the Hyperbolic Arc Sine of Series. Hyperbolic Arc Tangent Put the hyperbolic arc tangent of the source field into the target field.   Example: Put in Temp the Hyperbolic Arc Tangent of Small. Hyperbolic Cosine Put the hyperbolic cosine of the source field into the target field.   Example: Put in Temp the Hyperbolic Cosine of Series. Hyperbolic Sine Put the hyperbolic sine of the source field into the target field.   Example: Put in Temp the Hyperbolic Sine of Series. Hyperbolic Tangent Put the hyperbolic tangent of the source field into the target field.   Example: Put in Temp the Hyperbolic Tangent of Series. Hypotenuse The great and ancient Pythagorean theorem: The square root of the sum of the squares of the two sides of a right triangle.  Save into the target the value of the hypotenuse taking two given source fields as DX and DY.   Example: Put in Temp the Hypotenuse taking Numbers as DX and Fractions as DY. Example: Put in Temp the Hypotenuse taking the number 3 as DX and 4 as DY. JSON Number A means to extract numbers that are embedded into JSON strings as the numeric value for a named JSON entity.  Scan the source field to find the specified name and if it exists and has a numeric value put the number into the target field.   Example: Find the JSON Number for the name 'FalseEasting' within the field CoordSys and put it into Temp. Example: Find the JSON Number for the name 'Eccentricity' within the field CoordSys and put it into Temp. The example table above has a field called CoordSys that contains JSON strings which specify various coordinate systems.   The JSON strings are long, the one for the first record above being:   { "Name": "Argentina Campo Inchauspe Faja 3", "System": "Transverse Mercator", "CenterLat": -90, "CenterLon": -66, "FalseEasting": 3500000, "Axes": "XYH", "Base": "Campo Inchauspe (Argentina)", "MajorAxis": 6378388, "Eccentricity": 0.08199188997902977, "Transform": "Molodensky-Badekas", "CenterX": -148, "CenterY": 136, "CenterZ": 90, "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }   Some of the named entities have numeric values and some have string values.  Using the JSON Number template we can extract numeric values for any of the named entities.   In the first example there is no FalseEasting name in the JSON string for the third record so the result is NULL.  In the second example all of the records have Eccentricity names in their JSON strings. Length Given a geom that contains a line object or area object, place the length of that line object into the target field.  The length of an area object is the length of its boundary, that is, the perimeter of the area.  The length reported for a branched object is the sum of the lengths of the branches. Log-gamma Function The log-gamma function takes the natural logarithm of the gamma function for the source field and stores the result into the target field.   Example: Put in Temp the Log gamma Function of Small. Logarithm Put the base e logarithm (also called the natural logarithm) of the source field into the target field.   Example: Put in Temp the Logarithm of Numbers. Maximum Value Take the maximum, that is the greater value, of either the source field or the comparison field and save into the target field.   Example: Put in Temp the Maximum Value of Numbers or Fractions. Minimum Value Take the minimum, that is the lesser value, of either the source field or the comparison field and save into the target field.   Example: Put in Temp the Minimum Value of Numbers or Fractions. Modulo The remainder (of a congruence) function: Take what is left over by the integral division of the source field by the given Divide by value and place into the target field.   Example: Put in Temp the Modulo of Numbers using 10 as the Divide by value. In the above we can see that 1474 can be integrally divided by 10, that is evenly divided, 147 times, leaving a remainder of 4. Multiply Multiply the two source fields and place the result into the target field.   Example: Multiply the Numbers field with Fractions and put the result in Temp. Negate Multiply the source field by -1 and place the result into the target field.   Example: Negate the Numbers field and put the result in Temp. Number of Branches Given a geom field report the number of branches for the object the geom contains. Number of Characters Count the number of characters in a source text field and put the result into the (numeric) target field.   Example: Count the Number of Characters in Description and put the result in Temp. Number of Coordinates Given a geom field report the number of coordinates which define the object the geom contains.  A rectangular area objects contains 5 coordinates because the last coordinate is the same as the first to close the area. Number of Regexp Matches Given a text source field, count the number of times the given regular expression Pattern matches within the source field and put that number into the numeric target field.  A value of -1 indicates no matches.   The regular expression Pattern of '.' matches any character.  Given a string value of 'Jason' the pattern will match 5 times within that string, matching each of the five characters that occur in that string.  The number 5 will be put into the target field.   Given a string value of 'Mississippi' and a regular expression Pattern of 'i' the pattern will match 4 times within that string and the number 4 will be put into the target field.   Example: Put into Temp the Number of Regexp Matches using Country and a regexp pattern of 'e'. The example counts the number of occurrences of the letter e, which occurs just once in Switzerland, France and Germany but twice in Netherlands. Power Compute the source field to the power of the specified Power value and put the result into the target field. A Power of 2 is the source field squared while a Power of 3 is the source field cubed.   Example: Take the Power of Numbers using a Power of 3 and put the result in Temp. Power of 10 Compute 10 to the power of the source field and put the result into the target field.  If the source field has values 1, 2, 3,... the result of Power of 10 will be 10, 100, 1000,....   For anything but small numbers in the source field such exponentiation very rapidly results in numbers larger than can be represented in most types and thus stores a NULL in the target.     Example: Put in Temp the Power of 10 of Series. Power of 2 Compute 2 to the power of the source field and put the result into the target field.  If the source field has values 1, 2, 3,... the result of Power of 2 will be 2, 4, 8,....   For anything but small numbers in the source field such exponentiation very rapidly results in numbers larger than can be represented in most types and thus stores a NULL in the target.     Example: Put in Temp the Power of 2 of Series. Reciprocal Cube Root Also known as the inverse cube root: Take 1 divided by the cube root of the source field and put the result into the target field.   Example: Put in Temp the Reciprocal Cube Root of Numbers. Reciprocal Square Root Also known as the inverse square root: Take 1 divided by the square root of the source field and put the result into the target field.   Example: Put in Temp the Reciprocal Square Root of Numbers. Regexp Match Position Given a text source field, determine the first character position within the text that the given regular expression Pattern matches put the number into the numeric target field. A value of -1 indicates no match.   The regular expression Pattern '.' matches any character.  Given a string value of 'Jason' the pattern will match the very first character, that is, the 0 position (we are using a counting system that begins with zero, for 0, 1, 2,...).  The number 0 will be put into the target field.  Using a regular expression Pattern of 'a' will match the second character, an a character, that is in the 1 position so the number 1 will be put into the target field.     Example: Put into Temp the Regexp Match Position using Country and a regexp pattern of 'e'. The regexp pattern 'e' matches any occurrence of the letter e, which occurs just once in Switzerland, France and Germany but twice in Netherlands.  In the first record the first e occurs in position 1, the second character position when counting 0, 1, 2, ...  but in the second record the first e occurs in position 5, that is, the sixth character position. Regexp Match Position, Nth Given a text source field and a regular expression Pattern and an Occurrence number, n, (zero based counting, with the first Occurrence being 0, the second being 1 and so on), report the number of the character position in the text where the Pattern matches the nth time. A value of -1 indicates no match or no match for the given Occurence position.   Given a string value of 'Mississippi' and a regular expression Pattern of 'i' the pattern will match 4 times within that string.  If the Occurrence number is 2, meaning the third match, that third match occurs on character position 7 (also zero based counting, so that is the eighth character in the string).  The number 7 will be put into the target field.   Example: Put into Temp the Regexp Match Position, Nth using Country, a regexp pattern of 'e' and an Occurrence of 1. The regexp pattern of 'e' matches any occurrence of the letter e.  An Occurrence of 1 means the second occurrence when counting 0, 1, 2, ...   The parameters ask at which character position is located the second occurrence of the letter e.  For the countries of Switzerland, France and Germany there is no second occurrence of the letter e so the result -1 is put into Temp.  In the case of Netherlands the second occurrence of the letter e is located at the fifth character position, so (counting from 0) the result 4 is put into Temp. Round Round a source field with a fractional part up or down to the nearest integer and put the result into the target field.   Example: Round the Fractions field and put the result into Temp. Round to Decimals Round a source field with a fractional part up or down to the number of decimals specified by  Decimals and put the result into the target field.   Example: Round to Decmials the Fractions field using a Decimals value of 2 and put the result into Temp. Sign For a source field that is positive or negative put a 1 or -1 respectively into the target field.   Example: Determine Sign of the Numbers field and put the result into Temp. Sine Take the sine of the source field and put it into the target field.   Example: Put in Temp the Sine of Numbers. Size in Bytes Take the size in bytes of the value in the source field and put into the target field.  This will be the same number for numeric source fields (for example, 8 for float64 types) and will be a varying number for fields such as varchar fields.   Example: Put in Temp the Size in Bytes of Description. Square Root Take the square root of the source field and put it into the target field.   Example: Put in Temp the Square Root of Numbers. Subtract Subtract one source field from the other and put the result into the target field.   Example: Subtract the Fractions field value from Numbers and put in Temp. Tangent Take the tangent of the source field and put it into the target field.   Example: Put in Temp the Tangent of Numbers. Text Compare Order, Intl Compare text sort order of source field and comparison field. If the source field comes in a bigger sort order number than, that is, it appears after, the comparison put 1 in the target field.  If it sorts to a lesser sort order position, that is appears before, put -1 in the target.  If identical put a 0 in the target field.  An option box allows choosing the language used for sort order.   Example: Put in Temp the Text Compare Order, Intl result of comparing Comment to Description. In the first record a text value beginning with Dol... sorts after a text value beginning with Cet... so the result 1 is put into Temp.  In the second record a text value beginning with Dol... sorts before a text value beginning with Dou... so the result -1 is put into Temp. In the third record both fields are identical so the sort order is the same and the result 0 is put into Temp. Text Position Find the specified string in the text source field and count the character position number (beginning at 0 for the first character position) at which it is found and put that number into the target field.  If not found put -1 into the target field.   Example: Put in Temp the Text Position number searching for 'village' in the Description field. Only one match occurs above, in the third record where 'village' matches the contents of the Description beginning with the ninth character position for the number 8 (we are counting 0, 1, 2,...) to be put into Temp.  The other records have -1 in Temp to indicate no match. Text Position, Intl Same as Text Position but with optional specification of the language used. Text Position, Nth Given a text source field, a Search for string and an Occurrence number, n, (zero based counting, with the first Occurrence being 0, the second being 1 and so on), report the number of the character position in the text where the Search for string matches the nth time.   If not found at the specified nth position put -1 into the target field.   Given a string value of 'Mississippi' and a Search for string of 'i' the string will match 4 times within that string.  If the Occurrence number is 2, meaning the third match, that third match occurs on character position 7 (also zero based counting, so that is the eighth character in the string).  The number 7 will be put into the target field.   Example: Put in Temp the Text Position, Nth number searching for 'ou' in the Description field with an Occurrence number of 0 (meaning the first match). The string 'ou' matches Description in the first record at the 14th character position so the number put into Temp is 13.  The string 'ou' matches Description in the second and fifth records at the second character position so the number put into Temp for those records is 1. Truncate Take the integer part of the source field, discarding any decimal fraction part, and put the result into the target field.   Example: Truncate the Fractions field and put the result into Temp. Truncate to Decimals Take the integer part of the source field as well as any decimal fraction up to the specified number of Decimal places, discarding any decimal fraction part beyond, and put the result into the target field.   Example: Truncate to Decimals the Fractions field using a Decimals value of 2 and put the result into Temp. Vector Value Copy the specified part of a vector numeric Value source field into the target field, with an Index number specifying the part to extract. The parts of a vector numeric value are counted starting with zero: 0, 1, 2 or 3 for the first, second, third and fourth part respectively.  The examples below use a source field containing values that are triples of uint8 numbers, that is, vector numeric values with three numbers in the vector, each number of which is an unsigned, eight-bit integer.   Example: Put in Temp_Uint the first part (Index of 0) of the field uInt8x3. Example: Put in Temp_Uint the second part (Index of 1) of the field uInt8x3. Example: Put in Temp_Uint the third part (Index of 2) of the field uInt8x3. ## Notes

Everything Math - For a handy reference to anything in mathematics, see the Wolfram MathWorld site.   Thank you, Wolfram!

A guy walks into a bar and asks for 1.014 root beers.  The bartender says, “I’ll have to charge you extra, that’s a root beer float”.  So the guy says, “In that case, better make it a double.”

Why do programmers confuse Halloween with Christmas?  Because OCT 31 = DEC 25.

Transform Dialog

Transform Options

Transform Templates

Transform Templates - Drawings

Transform Templates - Images

Transform Templates - Binary

Transform Templates - Boolean

Transform Templates - Datetime

Transform Templates - Geom

Transform Templates - Text

Transform Templates - Tile

Transform: Center and Centroids

Transform: Escape Templates

Example: Two Drawings from the Same Table - Take a table with a geom field that is visualized by a drawing.  Add a second geom field to the table and create an rtree index on that field so it can be visualized by a drawing.   Copy the first drawing, paste it and adjust the pasted copy so it uses the second geom field. Demonstrate how to use the Transform dialog to show "live" modifications in the second drawing compared to the first drawing.

Example: Copy one Column into Another Column with Transform - How to use the Transform dialog to copy the contents of one column in a table into another column, but only for selected records.  Uses the Products table from the Nwind example data set.

Example: Transform Field Values using an Expression in the Transform Dialog -  How the Expressions tab of the Transform Dialog may be used to change the values of fields.   We include an example of changing the price of selected products and using two different Transform dialogs open at the same time for two different table windows.

Example: Construct JSON String using Select and Transform - Use the Select and Transform dialogs to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.

Example: Edit a Drawing with Transform Dialog Templates -  In this example we open a drawing and edit objects in the drawing using the Transform dialog Template tab.   Includes examples of using the Add Component button and also the Edit Query button.

Example: Use a Transform Dialog Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform Dialog to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.

Example: Clip Areas with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

Example: Smooth Lines with a Transform Dialog Expression - Use the Expression tab of the Transform dialog to make lines smoother in a drawing so that longer lines are smoothed more.  Includes examples of using the Edit Query button to show how different queries are created automatically depending on if we want to update a field or to add a new component.

Example: Transfer Options and Merge Areas - Using the Merge Areas Transform dialog template, an exploration of the difference between using Copy and Sum for transfer options.