Requirements for Measure Factory Lookup Tables

In Workbench 7.1(12) and earlier, Measure Factory lookup tables acquire supplementary data from text files only. In Workbench 7.1(13) and later, Measure Factory lookup tables can acquire supplementary data from any type of Spectre input. The lookup tables contain supplementary fields that the factory can use as reference data. For example, lookup tables can convert codes from the input data set to corresponding descriptions.

Lookup tables have one or more columns and one or more keys. The columns are the values to bring into the data set and the keys are a common data point between the data set and lookup table.

Versions prior to 7.1(13) require the following columns in the lookup table:

__mf_start_date
__mf_end_date

These start and end date values allow you to change the mapping of keys and columns over time.

A null start date is equivalent to the beginning of time.
A null end date is equivalent to the end of time.

NOTE: In Workbench 7.1(13) and later, these columns are no longer required. If one of these columns does not exist, any lookups against the table do not need the date tag to specify a date rule.

Sample lookup table

__mf_start_date	__mf_end_date	Medical Code	Medical Procedure	LOS
2021/10/01	2022/10/01	467	REVISION OF HIP OR KNEE REPLACEMENT WITH CC	3.3
2022/10/01		467	REVISION OF HIP OR KNEE REPLACEMENT WITH CC	3.5
2021/01/01		468	REVISION OF HIP OR KNEE REPLACEMENT WITHOUT CC	1.9
2021/10/01		469	MAJOR HIP AND KNEE JOINT REPLACEMENT OR REATTACHMENT OF LOWER EXTREMITY WITH MCC OR TOTAL ANKLE REPLACEMENT	4.6
2021/10/01	2021/10/01	470	MAJOR HIP AND KNEE JOINT REPLACEMENT OR REATTACHMENT OF LOWER EXTREMITY WITHOUT MCC	2.8
2022/10/01		470	MAJOR HIP AND KNEE JOINT REPLACEMENT OR REATTACHMENT OF LOWER EXTREMITY WITHOUT MCC	3.1

In Workbench 7.1(13) and later, you can use an input tag to identify alternative data sources for the lookup. A path identifies a specific file, and an input tag specifies the input type. A significant advantage of specifying an input is that you can perform operations similar to those allowed in a Spectre Build script.

If you do not use the input tag, the lookup table must be stored in the config directory and have the following file name format: lookup_table_<name>.txt. The lookup_table_ identifies the txt file as a lookup table, and the <name> is used to reference the specific lookup table.

NOTE: Lookup table text files are tab delimited with column headers.

The lookup-tables section of the factory configuration file can include one or more lookup-table blocks. For example, two lookup table files named lookup_table_Revenue.txt and lookup_table_Transaction.txt are referenced in the following code sample:

lookup-tables {

lookup-table "Revenue" {

column "Revenue Description" type = "string"

key "Revenue Code"

}

lookup-table "Transaction" {

column "Transaction Description" type = "string"

key "Transaction Code"

key "Source System"

}

The following code example references lookup table files named contacts.cbase and revenue-codes.txt with input tags. This example also uses alternative names for the start and end date columns by using the date-range-columns tag.

lookup tables {

lookup-table "contact-info" {

cbase-input "/cbases/contacts.cbase" {

replace "Name" `capitalize(value("Name"))`

}

key "Name"

column "Phone Number" type = "string"

}

lookup-table "revenue-codes" {

text-input "/data/revenue-codes.txt"

key "Revenue Code"

column "Description" type = "string"

date-range-columns "Start Date" "End Date"

}