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.

Note: Please note that a coordinate transformation must be performed for both the transformed longitude and latitude.

Source
Northing/Latitude Column from which the high value or latitude to be transformed is read.
Easting/Longitude Column from which the legal value or longitude to be transformed is read.
coordinate system

coordinate system in which the coordinates to be transformed are interpreted

Result
Caption Enter a name for the new column
Target coordinate Specifies whether the latitude or longitude is to be calculated.
Coordinate System The coordinate system into which the transformation is to be performed.

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.

The distance function is identical to the calculation function "Distance(...)".

Source
Latitude (LAT) Column with the latitude of the initial coordinate.
Longitude (LON) Column with the longitude of the initial coordinate.
Latitude (LAT) Column with the latitude of the final coordinate.
Longitude (LON) Column with the longitude of the final coordinate.
Result
Caption Name of the newly created column that represents the distance between the two coordinates.

Application examples:

You have stored the address coordinates of your customers in a customer table and 2 further columns with the coordinates of the sales representative in charge. This function can be used to conveniently determine their distance from each other.

... or you have 2 location levels in an area organization for an territory level, for example as a comparison between your grown structure and an implemented location optimization. The distances between the original and the optimized locations can be easily calculated using the Distance function.

If you use a point plane as a basis for your territory structure, easymap also generates a coordinate for each point. For example, if you create the locations of pharmacies or hospitals as point planes, it is possible to determine the distance to the pharmaceutical sales representative's place of residence.

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.

You must make the following settings:

Source
Source Table Selected table (informative only)
Reference Table

Selection of the reference table from which a value is to be read out

Note: Queries based directly or indirectly on the source table are not available here, since otherwise circular references could occur.

Key source table Column from the source table used to match a column in the reference table.
Key reference table Column from the other table that is to be used to find a suitable row in the other table.
Key matching ExactlyOnly rows whose key exactly matches the key in this table are selected from the other table. If one value is formatted as text and the other as number, the line is no longer matched.

Numerical: The comparison of both columns takes place in a generous way. Leading spaces, zeros, etc. are ignored, formatting as text or number does not matter. This is the recommended setting.
Result
Caption Defines the name of the column. If you do not specify anything here, easymap automatically selects a name
Value Column Column whose values are read and output as the result of this calculated column.
Aggregation It can happen that several rows for the key you are looking for are found in the reference table. Their values from the value column must be combined to form a result value. The aggregation mode specifies how this is done.

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.

Source
Position of the test points

Here you must specify how a position in the map is to be determined from the current row in the table. The number is in the column and the level must have the same spatial reference so that the table can be positioned on a map of your choice.
Area Layer

Here you enter the territory level for which you want to determine the Located in. For each position that is determined, the column checks in which area of this level the point falls. If the position does not fall in any of the areas, the result remains empty.
Result

Caption

Enter a name for the new column.

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.

Source

Symbol Analysis

The analysis whose symbols are counted is specified here. Once an area has been determined - as described under Territory level - all symbols that lie geographically in this area are searched for. The number of these symbols is then output as the result.
For which areas should the number of symbols be determined?

Here you must specify how a position is to be determined from the current row in the table. To do this, an territory level must be specified as well as the column that contains the number. This number is read for each row and the territory area is searched for in the territory level. If an area with the specified number is not found, the result of the calculation is empty.
Result

Caption

Enter a name for the new column

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).

Select the columns for calculating the scores here. Only number columns of the table are available for selection, since, you can only perform calculations on them.

Source

By checking the boxes you select the Columns from which the score should be calculated. In the Weight column, enter the percentage to which the column is to be included in the calculation. Changing the weight adjusts the blue bar accordingly.

Calculation Scheme

For the calculation schema, you can choose between percentage and absolute score calculation:

  • You can always use an absolute score if the individual data columns used in the score calculation show the same or at least comparable units, such as the number of A, B, and C customers, or turnover/potential of different products in Euro.
  • The choice of percentage score-calculation, on the other hand, allows to calculate a (weighted) code number from data columns which show completely different units. If, for example, one would like to reduce the number of visits in larger areas because of the longer travel times, one could form a percentage score from the number of visits (100%) and the territory area (10%). For smaller areas, this would increase the number of visits, for larger areas, it would decrease. For areas of the same size, the area share in the score value would have no effect.

Result

Enter a name for your score.

The following calculation is performed in the background:

Absolute Score Addition of the values multiplied by their weights
Percentage Score Addition of the column proportions multiplied by their weights. The column proportions are obtained by dividing the values by the column total.

Note: very well as the basis of a multicriterial area optimization.

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.