GetCDMTableId¶

mapping category: cdm-helpers no `||`

Looks up a value in a CDM table by key and replaces the input with the matching value.

Maps the input using two fields in a single CDM table — one acts as a key, the other as a value. If the input matches the key, it is replaced with the corresponding value. If the key cannot be found and no default is given, the record mapping fails.

A common use is to map IDs in a source table to IDs in the OMOP CDM. The best practice is to store the source ID in <table>_source_value and the CDM table id in <table>_id; these are the default key and value field names used when only the table is specified.

Parameters¶

Default fields, optional default value¶

1 — table Mandatory — accepts: table: The CDM table to collect the key and value from, in @-notation. With this form the key field defaults to <table_name>_source_value and the value field to <table_name>_id.
2 — default Optional — accepts: any: A default value used when the key cannot be found. The type must match the value field's type.

Explicit key/value fields¶

1 — table Mandatory — accepts: table: The CDM table, in @-notation.
2 — key Mandatory — accepts: field: The key field on the CDM table, in :-notation.
3 — value Mandatory — accepts: field: The value field on the CDM table, in :-notation.
4 — default Optional — accepts: any: Default value used when the key cannot be found. The type must match the value field's type.

Input type¶

The input type must match the type of the key field.

Output type¶

The output type matches the type of the value field.

Requires¶

The table used must exist.
Either both the key and value field are specified or neither is.
Both fields must be available in the table.
If a default value is set, its type must match that of the value.

Examples¶

Default fields (<table>_source_value, <table>_id)

GetCDMTableId(@location)

Explicit key and value fields

GetCDMTableId(@location, :location_source_value, :location_id)

Default value when key not found

GetCDMTableId(@location, 0)

Explicit fields with default

GetCDMTableId(@location, :location_source_value, :location_id, 0)