The correlate statement is used to enrich the default DSET by adding new columns to it, and/or updating existing columns with useful values. The new column names are derived from other DSETs and the values in those columns are set using a lookup function based on the value in a key column shared between the DSETs.
correlateColName1 [ ... ColNameN]usingKeyColumn[assumingassumeDSET][defaultDefaultValue]
The ColName1 ... ColNameN arguments are column names that will be copied from their original DSETs and merged into the default DSET.
Column names must be fully qualified, unless the assuming parameter is used, in which case any column names that are not fully-qualified will be assumed to belong to the DSET specified by assumeDSET.
Source columns are those from which a cell is to be copied when the KeyColumn matches. Destination columns are columns in the default DSET into which a cell will be copied. Destination column names are derived from the names of the source columns as follows:
The source column is the argument in its original form, for example: Azure.usage.MeterName
The destination column is the same argument, but with the DSET ID replaced with that of the default DSET. For example if the default DSET is Custom.Services then the destination column for the above would be Custom.Services.MeterName.
If a destination column name doesn't exist in the default DSET then a new column with that name will automatically be created.
The KeyColumn argument is a column name which must not be fully qualified and which must exist in the default DSET and all of the DSETs referenced by the ColNameN arguments.
The DefaultValue argument, if present, specifies the value to write into the destination column if there is no match for the KeyColumn. If the DefaultValue argument is not specified then any rows where there is no match will result in a blank cell in the destination column.
For each row in the default DSET, the source DSET is searched for a matching KeyColumn value, and if a match is found then the value in the source column is used to update the default DSET. The row of the first match found in the source DSET will be used.
When matching the KeyColumn values, the logic in the following table is evaluated against every row in the destination DSET.
✘ means no or disabled, ✔ means yes or enabled
Given two Datasets as follows, where the default DSET is MyData.Owners:
Dataset 'MyData.Owners'
Dataset 'Custom.Services'
The statement:
correlate service description using id assuming Custom.Services
Will enrich the MyData.Owners Dataset such that it contains:
The statement:
correlate service description using id assuming Custom.Services default unknown
Will produce:
Match Found
Overwrite
Default Value
Result
✘
✘
✘
No values will be updated
✔
✘
✘
Empty destination column cells will be updated
✘
✘
✔
Empty destination column cells will be set to the default value
✔
✘
✔
Empty destination column cells will be set to the matched source column value
✘
✔
✘
No values will be updated
✔
✔
✘
Destination column cells will be updated
✘
✔
✔
Destination column cells will be set to the default value
✔
✔
✔
Destination column cells will be set to the matched source column value