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 which 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 Substring column function is used to extract part of the cell contents 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 number (i.e. the district code number). The substring function is identical to the calculation function "Part(...)".
After you have selected your source, you can still decide whether the substring should be taken from the beginning, from the middle or from the end.
As Result you can directly label the new column.
Note: For example, if you want to have the content from a certain position, specify 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 geographical coordinates. If your coordinates are available in another system (in Germany, for example, Gauß-Krüger coordinates are common), you can add calculated columns of this type in order to be able to use such coordinates in analyses. Back-conversion is of course also possible.
The settings in detail:
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 Distance function is used to calculate the line distance in meters between two points or coordinates. The calculation assumes that for each of the two coordinates there is a column with the latitude (LAT) and a column with the longitude (LON).
The settings in detail:
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 level as the basis for your territory structure, EasyMap also creates a coordinate for each point. If, for example, you create the locations of pharmacies or hospitals as point levels, it is possible to determine the distance to the pharmacist³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.
You can call this function either in external data in table window or via query dialog.
The settings in detail:
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 | ExactOnly 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 a name 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
With the column Located in you can identify for a number or a coordinate into which area of another level this falls. For example, you have initially imported a client base table for an area structure and now want to know which employee is responsible for the customer after area changes.
The settings in detail:
| 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. |
Count of elements in
This column is used to identify for an area how many symbols of an analysis lie in this area.
The settings in detail:
| 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 under consideration of several data columns. Such scores are often used in order to be able to consider several evaluation or optimization criteria simultaneously in area and location analyses and optimizations (e.g. turnover, visiting time and area per sales area).
The settings in detail:
Here you select the columns which are to be used for the calculation of the score. Only number columns of the table are available for selection here, since a calculation can only be performed 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:
- A absolute score can always be used if the individual data columns used in score calculation have the same or at least comparable units, such as the number of A, B, C customers or turnover/potential of different products in euros.
- 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 description 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: You can use the score calculation in the EasyMap DistrictManager Edition very well as a basis for multicriterial area optimization.
Aggregation functions
At several points in EasyMap, a lot of data must be combined into one result value. The following functions are available in EasyMap:
Note: Empty values (empty table cells) are not taken into account except in aggregation mode Number of data records.
