Calculated Columns

Columns can be added in the tables for imported and linked external data. Various calculation functions and some special functions are available for this purpose. The calculated columns do not hold their own data, but determine a new value from the values of other columns. If the contents of other columns change, the calculation result can also change.

These calculations can also be inserted in queries or work tables (see Queries).

With this button, you can open a menu in the table view that offers the different calculation functions.

Calculated Column

Here you can use a formula editor to specify a calculation based on the contents of the table. A list of all functions and formulas can be found here.

Substring column

The function partial string column is used to extract a part of the cell content from another column. This is useful, for example, to generate a new column that contains only the first two digits of the postal code or only the first five digits of the eight-digit municipality code (i.e., the district code). The substring function is identical to the "Part(...)" calculation function.

After you have selected your source, you can still decide whether the substring should be taken from the beginning, the middle or the end.

As Result you can directly label the new column.

Note: For example, if you want to output the content from a certain position, enter Part and set the character for End accordingly high.

Coordinate Transformation

With this calculation you can have coordinates of one coordinate system converted into another.

The analyses in easymap always require geographic coordinates. If your coordinates are available in another system (e.g. Gauß-Krüger coordinates are common in Germany), you can add calculated columns of this type to use such coordinates in analyses. Retroactive conversion is also possible, of course.

In our Tips and Tricks you can find more information about this.

Distance

The function distance is used to calculate the linear distance in meters between two points or coordinates. The calculation presupposes that for each of the two coordinates in each case a column with the latitude (LAT) and a column with the longitude (LON) exists.

Lookup from Reference Table

Allows you to read a value from another table and display it in the current table. The relationship between the tables is established using two key columns.

The function is called either in external data in the table window or via the query dialog.

Located in Region

The Located In column can be used to identify the area of another level in which the data record falls. For example, you have a Customer Base input and a Territory structure and now want to know which employee is responsible for the customer after changes to the Territory.

Note: If a record is located in multiple areas, they will be listed out separated by a comma. Alternatively, you can also create a spatial link.

Number of elements in

This column is used to identify for an area how many symbols of an analysis lie in this area.

Score

The calculated column Score enables the determination of a key figure taking into account several data columns. Such scores are often used in order to be able to consider several evaluation or optimization criteria at the same time in area and location analyses and optimization (e.g. sales, visiting time and area per sales area).

Address

Determines a coordinate from various components of an address. The more components you specify, the more accurately the coordinate can be determined.

Note: If you do not have a special license, the coordinates of the address are not calculated exactly, but are drawn on a 100 m grid (Inspire 100 m ETRS-LAEA grid). This means that addresses located close together can fall to the same gridded coordinate.

The following address components can be specified in the Source area:

street: Column containing the street (with or without house number).
house number:

Either a column that only contains the house number, or the entry (house number in the Street field) that specifies that both the street and the house number are to be taken from the Street field. If a column is specified, a column must also be selected for the Street field.

Location: Column containing the city name. This must not contain any other things such as the postal code and should correspond to the official or postal spelling for the best result quality.
Postcode: Column containing the postal code. This must not contain any other things such as the place name.
Country: Column containing the country (preferably the official postal country name). If you specify a column here, you can process international addresses that are distributed across several countries. If no column is specified here, national addresses from a country specified under Country (default) are processed. This country is also used if a column is specified but there is no value in a row in this column.
Country (default): Country to which the addresses are assigned that are not otherwise assigned to another country via the column defined in Country.

The information, either a postal code or a city, is mandatory. A standard country must also be specified.

You can use in easymap office you can only process addresses from Germany, Austria and Switzerland in easymap. You can geocode worldwide addresses in easymap professional. easymap professional You still have an old license and would like to access worldwide encoding as well? Then please contact us.

If a "Country" column is selected, the following entries can be processed:

  • Country advertised in German or English spelling (e.g. "Switzerland", "Austria")

  • Vehicle nationality signs (e.g. "D", "A", "CH")
  • Two-letter country code (ISO 3166-1 alpha-2) (e.g. "de", "at", "ch")
  • Three-letter country code (ISO 3166-1 alpha-3) (e.g. "deu", "aut", "che")

If no house number is specified, any address on this street is selected. If no street is specified, a point is returned to the found city.

In all cases, a similarity comparison is performed. Thus it is not excluded that a city or a street with a similar name will be found for the determination of the coordinate if no exact match can be found.

Aggregation functions

At several places in easymap a lot of data has to be combined to one result value. The following functions are available in easymap:

aggregate function Example result for (6,7,1,4,7,2) Returns a value from the input Available for text values
Sum 27
Average 4,5
Minimum 1
Maximum 7
First value1 6
Last value2 2
Median3 6
Most Frequent Value4 7
List (separated by commas)5 1, 2, 4, 6, 7
List (system separator)6 1;2;4;6;77
Number of Records8 6
Number of values9 6

Note: Empty values (empty table cells) are not taken into account except in aggregation mode Number of data records.