Understanding Data Types

Overview

All attributes have a data type. When loading data from a source, such as a file or database, PhixFlow applies the data type from the source. If there is no data type specified, PhixFlow applies the default type of String.

You can change the type of an attribute before the data has been loaded into the table.

Once the table contains records, you may be able to change the attribute's data type, depending on the database on which PhixFlow runs. PhixFlow reports an error if you are not permitted to change the data type. In this case you can rollback all recordsets so that the table no longer contains records; see Rollback Recordsets.

String

Apply a string type to sequences of characters, such as letters, numerals, symbols and punctuation marks. By default string attribute types have a default length of 50. Specify a different string length in Basic Settings → Length.

Bigstring

Apply a bigstring type to text strings over 4000 characters long. Bigstring is a different data type to string. For PhixFlow instances that use an Oracle database, bigstrings:

  • cannot be sorted or aggregated
  • can only be filtered with the conditions:
    • (not) contains
    • is (not) null
    • (not) starts with
    • (not) ends with.

Bigstring attributes cannot be indexed or set as a   Primary key as this isn't supported by PhixFlow's underlying database.

The PhixFlow administrator can set the length of a bigstring using System Configuration → System Tuning → Maximum Bigstring Size.

Integer

Apply an integer type to whole numbers (not fractions or decimals) that can be positive, negative, or zero. By default integers have a precision of 10 digits. Specify a different integer length in the Basic Settings → Precision.

 If you store a number with decimal places as an integer, it will be truncated.

Float

Apply a float type to non-integer numbers with an undefined level of precision.

Decimal

Apply a decimal type to non-integer numbers with a set level of precision.


PhixFlow restricts float or decimal numbers to 15 significant figures when they have 15 decimal places and there are many digits before the decimal point.

Decimals have a specific number of:

  • significant figures: the total number of digits stored, 
  • decimal places: the number of digits after the decimal place.

The maximum number of integer digits is therefore the significant figure minus the decimal places. By default, decimals have 10 significant figures and 2 decimal places, and therefore 8 integer digits.

If the number of integer digits present in a decimal field exceeds the specified format, running analysis will fail and PhixFlow will report an error.

If the number of decimal digits present in a decimal field exceeds the specified format, PhixFlow rounds to the required precision. For example, if a data record includes the number 12.3456, but the decimal type can have 4 significant figures and 2 decimal places, PhixFlow records 12.35.

Ensure your decimal type has the number of decimal places you need for your data. Once the data is loaded into PhixFlow, you will not be able to recover excess decimal places.

The PhixFlow administrator can set the default number of decimal places in System Configuration → General Settings → Decimal places.

Dates

Apply a date type to representations of a calendar day. Word formats, such as Tuesday the fourteenth of February are not recognised. However, three letter month abbreviations are recognised, for example: 14 Feb 2019.

The PhixFlow administrator can set the default date format in System Configuration → General Settings → Default Date Format.

If the System Configuration or Number Format option to Suppress Trailing Zeroes is set, PhixFlow will display decimals and floating point numbers without trailing zeroes.

Recognised Date Formats

PhixFlow recognises:

  • the international standard format  yyyyMMdd, for example 20190214. 
    This date has no separators.
  • the following variations, in which:
    • y represents year digits
    • M represents month digits or MMM represents three letters
    • d represents day digits

The examples are shown with space separators. Other recognised separators are:

  • forward slash /
  • hyphen -
  • or a space.
14 02 201914 Feb 201902 14 2019Feb 14 2019
dd/MM/yyyydd-MMM-yyyyMM/dd/yyyyMMM/dd/yyyy
dd-MM-yyyydd/MMM/yyyyMM-dd-yyyyMMM-dd-yyyy
dd MM yyyydd MMM yyyyMM dd yyyyMMM dd yyyy


MM d yyyyMMM d yyyy
14 02 1914 Feb 1902 14 19Feb 14 19
dd/MM/yydd/MMM/yyMM/dd/yyMMM/dd/yy
dd MM yydd-MMM-yyMM-dd-yyMMM-dd-yy
dd-MM-yydd MMM yyMM dd yyMMM dd yy


MM d yyMMM d yy

Datetime

Apply a datetime type to representations of a day and a time. It cannot be used to represent a time alone.

Datetimes can have any of the above date formats followed by a space separator and HH:mm:ss. for example MM/dd/yy HH:mm:ss represents a datetime such as 02/14/19 15:35:06.

  • H represents hour digits (24 hour)
  • m represents minute digits
  • s represents second digits
  • time separators are colons :

The international datetime uses a full stop/period as a separator between the date and time, yyyyMMdd.HHmmss, for example 20190214.15:35:06.

PhixFlow recognises date and date-time formats, but does not recognise time-only formats, for example HH:mm:ss. When a database collector loads time data, it applies the string data-type. This applies to data from MySQL/ MariaDB and SQL Server databases as there is no TIME data-type in Oracle DB.

The PhixFlow administrator can set the datetime format in System Configuration → General Settings → Default Date/Time Format.

Graphic

Apply a graphic type to fields that contain image files.

True or False Values

Apply the Basic SettingsType of TrueFalse to fields that can have the values that represent true or false.

The values that represent true are:

  • any number other than 0
  • for a list: all items in the list are true
  • the following case-insensitive strings
    • true
    • t
    • yes
    • y

The values that represent false are:

  • 0 (zero)
  • any string not listed as true above including false

  • an empty/null field.

Structured Data 

This data type is only available for attributes in the following actionflow nodes and their connection points:

  • Calculate node
  • Analysis node
  • JSON node
  • XML node

Use the structured data type where an attribute contains:

  • a list (an array of values)
  • values in different formats, such as an integer or a string
  • values in a format that is not consistent with any other data type.

In an actionflow, a structured data attribute is useful when you need to pass a list of identifiers through to another actionflow node or out of the actionflow, to a screen.

For an attribute with a Type of Structured Data, PhixFlow does not check that the data value is consistent with the attribute type.

PhixFlow cannot pass a structured data attribute to a Save node.

Geometric Data Types

Functions have been created to perform geometric operations on "Well Known Text", geometric representations of points, lines and polygons.

Well Known Text

Well known text (WKT) is a text mark up language for representing vector geometry objects. Most geometric functions accept a string containing well formed WKT syntax. These are known as WKT strings. For example, "POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))".

WKT is commonly generated and used by Geographic Information Systems (GIS) or geographical databases. 

For more information on this topic see: Well Known Text

Geometry

Many geometry functions return a "Geometry" object this is the internal optimised representation of the results which can be passed directly into other geometry functions.

Where the final result of an expression is a geometry object then this will automatically be converted into a String once evaluation is complete.

To convert a Geometry object into a Well Known Text* string, so that it can be passed to other non-geometry functions toString() must be called.

See Geometric Functions for more detail.

File

File is a special data type that is used exclusively with File Display Screen Component and the the table of type File Table. File houses file information in the form of a preview.