MapMissingFieldsFromTable

Used when standardising variable data input sources to a specific data model.  This can be necessary if fieldnames are variable and/or data may be missing in an input file but the script needs to process the file anyway.

The method assumes that a mapping table has been created that has a list of expected standardised field names which must exist for some part of the script to work (stored in this example in the [01_DATA_MAPPING].[DATAMODEL] field, and referenced by the method as “targetField”).    

The method can work directly with the DATAMODEL field (in which case sourceField would be DATAMODEL) or it can use a lookup field (for example ExportField), in which case sourceField would be ExportField.  This can be helpful if outputs from the script are also variable.

The logic is as follows: 

For each value X of sourceField on table sourceTable:
if not blank:
get targetField Y on table sourceTable
If Y does not exist on targetTable
Make Y on targetTable

{
"method": "MapMissingFieldsFromTable",
"description": "Create Missing Fields required for Export Template",
"sourceTable": "01_DATA_MODEL",
"sourceField": "ExportField",
"targetField": "DATAMODEL",
"typeField": "Data Type",
"targetTable": "02_TRANSFORMS",
"makeValidFieldNames": true,
"project": "DATA_MAPPING"
}

For example:
DATAMODEL                          ExportField                DataType
DATAMODEL_VALUE               Product Value           String
DATAMODEL_NAME               Product Name          String
DATAMODEL_ID                      Product ID                String
DATAMODEL_PLACE                                                String
DATAMODEL_DATE                 Purchase Date          Date

For the data above,  assuming non DATAMODEL_X fields exist on the target table, the following fields would be created:  

DATAMODEL_VALUE
DATAMODEL_NAME
DATAMODEL_ID
DATAMODEL_DATE

Created fields are filled with NULL data.