DataLens data types

DataLens uses multiple data sources whose fields may take different data types.

To streamline the data operations, DataLens converts the source data types to its own types.
This optimizes data operations. For more information, see Data type matching table.

You can change the field data type in the dataset interface and in the wizard.

Geopoint

A coordinate point defined by latitude and longitude. In DataLens, it's set using the GEOPOINT function.

As an input, the function accepts the String and Geopoint data types, or two values of the Fractional number or String type.
If a single string is input, it must contain a list of two numbers in JSON format.

If the source data is a string like "[55.75222,37.61556]", you can use the dataset interface or wizard to change the field data type without a formula.

Note

You can use ready-made geodata sets in DataLens format from Geointellect, our partner.

The archive contains the following data:

  • Countries (polygons and points).
  • Russian regions (polygons and points).
  • Russian cities (points).
  • Districts of million-plus cities (polygons).

Example notation

GEOPOINT("[55.7912,37.6872]")GEOPOINT("[55.8538,37.6312]")

Geopolygon

Several coordinate points defining the polygon on the map. In DataLens, you create one using the GEOPOLYGON function.
The Even-Odd algorithm is used to fill in the polygon. This way you can create polygons with holes.

The function accepts strings in "[[[v1,v1], [v2,v2]], ..., [[vN-1,vN-1], [vN,vN]]]" format. If the source data is in this format, you can use the dataset interface or wizard to change the field data type without a formula.

Note

You can use ready-made geodata sets in DataLens format from Geointellect, our partner.

The archive contains the following data:

  • Countries (polygons and points).
  • Russian regions (polygons and points).
  • Russian cities (points).
  • Districts of million-plus cities (polygons).

Example notation

/* Polygon without a hole */GEOPOLYGON("[[[55.79421,37.65046],[55.79594,37.6513],[55.79642,37.65133],[55.7969, 37.65114],[55.79783, 37.65098],[55.78871,37.75101]]]")/* Polygons with a hole */GEOPOLYGON("[[[55.75,37.52],[55.75,37.68],[55.65,37.60]],[[55.79,37.60],[55.76,37.57],[55.76,37.63]]]")GEOPOLYGON("[[[55.75,37.50],[55.80,37.60],[55.75,37.70],[55.70,37.70],[55.70,37.50]],[[55.75,37.52],[55.75,37.68],[55.65,37.60]],[[55.79,37.60],[55.76,37.57],[55.76,37.63]]]")

Date

Date without specified time.

When you use a date in formulas, make sure to use hash # around it. For example, DATETRUNC(#2018-07-12#, "year", 5).

You can convert the source data type to Date using the DATE and DATE_PARSE functions.

Example notation

#2018-01-18##2015-01-01#DATETRUNC(#2018-07-12#, "year", 5)DATEADD(#2018-01-12#, "day", 6)

Date and time

Date with a specified time (the value is not converted to UTC).

When you use date and time in formulas, make sure to use a double hash ## around it. For example, DATEADD(#2018-01-12 01:02:03#, "second", 6).

You can convert the source data type to Date and time using the DATETIME and DATETIME_PARSE functions.

Example notation

##2018-01-12 01:08:03####2018-05-01T##DATEADD(##2018-01-12 01:02:03##, "second", 6)DATETRUNC(##2018-07-12 11:07:13##, "month", 4)

Floating-point number

Decimal number. The dot is used as a decimal separator.

You can convert the source data type to a Floating-point number using the FLOAT function.

Warning

When you convert the decimal type to Floating-point number, you may lose precision.

Example notation

1.471130.62024FLOAT("34.567")

Boolean

A boolean type that can take one of two possible values: TRUE or FALSE.

You can convert the source data type to Boolean using the BOOL function.

Example notation

FALSE

String

A string containing text. It is enclosed in single or double quotes.
If you enclose your string in one type of quotes, you can use the second quote type inside the string without escaping.
For example, 'Double quoted "example" and one " double quote.'

You can use the following characters inside strings:

  • \n: Line feed (LF).
  • \r: Carriage return (CR).
  • \t: Tab.
  • \": Double quote.
  • \': Single quote.
  • \\: Backslash.

You can convert the source data type to String by using the STR function.

Example notation

"String"'String with a "substring"'

Integer

A number with no no fractional part.

You can convert the source data type to Integer by using the INT function.

Example notation

42157

Array

A structure made up of a series of elements of the same type.

DataLens supports the processing of columns in one-dimensional arrays for the following databases: ClickHouse and PostgreSQL. Array elements can be the following types:

  • Integer
  • Floating-point number
  • String

Warning

You cannot define an array inside DataLens.

Example notation

[1, 2, 3][1.1, 1.2, 1.3]["Cheese", "Bread"]

Tree of strings

DataLens lets you create tree hierarchies. To do this, use the Tree of strings field type. Data is stored as an array with a full path for each node. The sequence of array values determines in what order the tree is expanded.

Note

A tree can only be used in a Table chart.

Creating a tree

To create a tree, add a calculated field at the dataset or chart level using the formula TREE(ARRAY([lev_1],[lev_2],[lev_3],[lev_n])), where [lev_1],[lev_2],[lev_3], and [lev_n] are dataset fields that determine the tree hierarchy.

Example of creating a tree for a source DB that contains no data array
  1. Prepare data in the source:

    1. Create a table with columns containing hierarchy values.

      Example of creating a table in PostgreSQL
      CREATE TABLE table_without_tree (   id serial primary key,   country text,   region text,   city text);
    2. Add data with a full path for each tree node to the table.

      Example of adding data to PostgreSQL
      INSERT INTO table_without_tree (country, region, city)VALUES('Russia', 'Altay', 'Barnaul');INSERT INTO table_without_tree (country, region, city)VALUES('Russia', 'Altay', 'Biysk');INSERT INTO table_without_tree (country, region, city)VALUES('Russia', 'Altay', 'Aleisk');
  2. In a dataset, create:

    • Array of strings calculated field that describes the tree. For example, the position field with the ARRAY([country], [region], [city]) formula.

    • Tree of strings calculated field. For example, the hierarchy field with the TREE([position]) formula, where position is a field of the Tree of strings type describing the tree.

      Tip

      You can create an array and tree of strings in one field with the TREE(ARRAY([country], [region], [city])) formula.

Example of creating a tree for a source DB that contains an array of data
  1. Prepare data in the source:

    1. Create a table with an array of strings.

      Example of creating a table in PostgreSQL
      CREATE TABLE table_with_tree (  id serial primary key,  position text[]);
    2. Add data as an array with a full path for each tree node to the table.

      Example of adding data to PostgreSQL
      INSERT INTO table_with_tree (position)VALUES('{"Russia","Altay","Barnaul"}');INSERT INTO table_with_tree (position)VALUES('{"Russia","Altay","Biysk"}');INSERT INTO table_with_tree (position)VALUES('{"Russia","Altay","Aleisk"}');
  2. In a dataset, create a Tree of strings calculated field. For example, the hierarchy field with the TREE([position]) formula, where position is a field of the Tree of strings type describing the tree.

Using trees in charts

You can use the prepared source data to create a tree in a Table chart:

  1. Create a Table chart.

  2. Drag a dimension with the Tree of strings type to the Columns section. You'll see a tree hierarchy in the visualization area. Expand or collapse the tree using + or -, respectively.

    Note

    You can only use tree dimensions in the chart.

  3. Add the required measures to the Columns section.

    image

Limitations

In the current version, a tree hierarchy has the following limitations:

  • One tree/hierarchy per chart.
  • You can't use the Pagination option and Total row in a tree table.
  • The tree expansion state is not remembered.
  • Selectors do not support trees.
  • A source should store intermediate tree nodes, even those with no data available.