Mapping

LEaT provides the option of performing a simple map, akin to an Excel VLOOKUP, on an array manually defined within the transformation. Assume we have the following table for this example.

DB::table('map_source')->insert([
    ['status_code' => 'A', 'type_id' => 1, 'category' => 'cat1'],
    ['status_code' => 'I', 'type_id' => 2, 'category' => 'cat2'],
    ['status_code' => 'X', 'type_id' => 3, 'category' => 'cat3'], // Unknown status
    ['status_code' => null, 'type_id' => 99, 'category' => null],
]);

Simple Mapping

$run = ExtractAndTransform::transform('Map String String')
    ->from('map_source')
    ->select([
        'status_code' => 'status_code',
        'type_id' => 'type_id',
        'category' => 'category',
        'status_label' => Expr::map('status_code', [
            'A' => 'Active',
            'I' => 'Inactive',
        ])->default('Unknown'),
    ])
    ->toTable('map_result_1')
    ->run();

This will create the following table as map_result_1

status_code	type_id	category	status_label
A	1	cat1	Active
I	2	cat2	Inactive
X	3	cat3	Unknown
(null)	99	(null)	Unknown

Numeric Keys

Keys in the provided map table can be numeric. For example:

$run = ExtractAndTransform::transform('Map Numeric Keys')
    ->from('map_source')
    ->select([
        'original' => 'type_id',
        'type_name' => Expr::map('type_id', [
            1 => 'Type One',
            2 => 'Type Two',
        ])->default('Other'),
    ])
    ->toTable('map_result_2')
    ->run();

This will create the following table as map_result_2

status_code	type_id	category	type_name
A	1	cat1	Type One
I	2	cat2	Type Two
X	3	cat3	Other
(null)	99	(null)	Other

Running without a Default Value

With no default value provided, the map will return null for unmatched columns.

$run = ExtractAndTransform::transform('Map No Default')
    ->from('map_source')
    ->select([
        'original' => 'status_code',
        'mapped' => Expr::map('status_code', [
            'A' => 'Active',
        ]), // No default() called
    ])
    ->toTable('map_result_3')
    ->run();

This will create the following table as map_result_3

status_code	type_id	category	mapped
A	1	cat1	Active
I	2	cat2	(null)
X	3	cat3	(null)
(null)	99	(null)	(null)

Defining Maps Dynamically

To define a map dynamically, create an entry under the 'selects' attribute named as the new column name, with the following attributes:

$config = [
    'selects' => [
        'original' => [
            'type' => 'column',
            'column' => 'status_code',
        ],
        'status_label' => [
            'type' => 'map',
            'column' => 'status_code',
            'mapping' => [
                'A' => 'Active',
                'I' => 'Inactive',
            ],
            'default' => 'Unknown',
        ],
    ],
    'wheres' => [],
];

$transformation = Transformation::create([
    'name' => 'Dynamic Map Transform',
    'source_table' => 'map_source',
    'destination_table_pattern' => 'map_result_json',
    'configuration' => $config,
]);

$run = $transformation->run();

This creates a table called map_result_json_v1 with the following values:

original	status_label
A	Active
I	Inactive
X	Unknown
(null)	Unknown