Download all tutorial files

Introduction

Table-Specific Transforms affect only a single source in-memory table. The Transform tag needs to be inside a LoadTable section (which is specific to a source table), as shown in the example below. The code will be explained later in the article.

<Load>
    <LoadTable SourceTable="Population" TargetTable="REF_POPULATION" LoadStrategy="MERGE">
      <Transform>
        <FindReplace Column="Year" Find="18" ReplaceWith="2018"/>
        <FindReplace Column="Year" Find="19" ReplaceWith="2019"/>
      </Transform>
    </LoadTable>
</Load>

The main difference with a Global Transform is that in a Table Specific Transform, as the name suggests, we are targeting a specific table to make changes.

Example of Table Specific Transforms

In the folder T6_Table-Specific Transforms (you may download this by clicking the link at the top of this page) there will be three files, model, Pipeline and Shanola Cases with pop. The scope of this example is to demonstrate how we can target transforms to specific tables. In this example we will load two tables and make changes to one of them while we avoiding changing the other table.

The changes need to happen in the “Population” table, and specifically change the year from 18 to 2018 and from 19 to 2019, while avoiding changing the numbers 18 or 19, if any, in the “Cases” table.

Provided Dataset

Below are the two tables we need to load.

Pipeline

Method 1

This is a sample pipeline for this example. Use pipeline.xml file

Extract: we are using two GetExcel commands, one for each table. This will help us to treat each table separately.

Global Transform: All the blank rows are removed from both tables.

Table-Specific Transform: The transform happens inside the Load section. The SourceTable is set to Population, which is the table we want to change the 18 and 19 to 2018 and 2019 respectively.

At the end of the pipeline we are performing the usual mappings for both tables.

<XmartPipeline IsStructure="false">
  <Extract>
    <GetExcel TableName="Cases" FindStartingRow="iso3, suspected, confirmed" OutputTableName="data" />
    <GetExcel TableName="Population" FindStartingRow="iso3, year" OutputTableName="Population"  />
  </Extract>
  <Transform>
    <CleanTable RemoveBlankRows="true" TrimValues="true" RemoveBlankColumns="false" />
  </Transform>
  <Load>
    <LoadTable SourceTable="Population" TargetTable="REF_POPULATION_T6" LoadStrategy="MERGE">
      <Transform>
        <FindReplace Column="Year" Find="18" ReplaceWith="2018"/>
        <FindReplace Column="Year" Find="19" ReplaceWith="2019"/>
      </Transform>
      <ColumnMappings>
        <ColumnMapping Source="ISO3" Target="ISO3" />
        <ColumnMapping Source="Year" Target="YEAR" />
        <ColumnMapping Source="Population" Target="POPULATION" />
      </ColumnMappings>
    </LoadTable>
    <LoadTable SourceTable="data" TargetTable="SHANOLA_CASE_COUNTS_T6" LoadStrategy="MERGE">
      <ColumnMappings>
        <ColumnMapping Source="ISO3" Target="ISO3" />
        <ColumnMapping Source="Date" Target="REPORT_DATE" />
        <ColumnMapping Source="Suspected (Males)" Target="SUSPECTED_MALES" />
        <ColumnMapping Source="Suspected (Females)" Target="SUSPECTED_FEMALES" />
        <ColumnMapping Source="Confirmed (Males)" Target="CONFIRMED_MALES" />
        <ColumnMapping Source="Confirmed (Females)" Target="CONFIRMED_FEMALES" />
      </ColumnMappings>
    </LoadTable>
  </Load>
</XmartPipeline>

Result

Here is the result for each of the target table. In the population you will see that the number 18 is changed to 2018 and this is how it is loaded in the target table and at the same time the number 18 remains for the data table.

Alternative Method - SetContext

Another approach is by using the SetContext command, which can be used to set each time which is the active table and what transformations you wish to make. This is useful when we need to make different transformations to each table. The result is the same as above.

You may use pipeline2.xml

<XmartPipeline IsStructure="false">
  <Extract>
    <GetExcel TableName="Cases" FindStartingRow="iso3, suspected, confirmed" OutputTableName="data" />
    <GetExcel TableName="Population" FindStartingRow="iso3, year" OutputTableName="Population"  />
  </Extract>
  <Transform>
    <CleanTable RemoveBlankRows="true" TrimValues="true" RemoveBlankColumns="false" />
    	<SetContext ActiveTables="Population" />
      		<FindReplace Column="Year" Find="18" ReplaceWith="2018"/>
        	<FindReplace Column="Year" Find="19" ReplaceWith="2019"/>
   </Transform>
  <Load>
    <LoadTable SourceTable="Population" TargetTable="REF_POPULATION" LoadStrategy="MERGE">
      <ColumnMappings>
        <ColumnMapping Source="ISO3" Target="ISO3" />
        <ColumnMapping Source="Year" Target="YEAR" />
        <ColumnMapping Source="Population" Target="POPULATION" />
      </ColumnMappings>
    </LoadTable>
    <LoadTable SourceTable="data" TargetTable="SHANOLA_CASE_COUNTS" LoadStrategy="MERGE">
      <ColumnMappings>
        <ColumnMapping Source="ISO3" Target="ISO3" />
        <ColumnMapping Source="Date" Target="REPORT_DATE" />
        <ColumnMapping Source="Suspected (Males)" Target="SUSPECTED_MALES" />
        <ColumnMapping Source="Suspected (Females)" Target="SUSPECTED_FEMALES" />
        <ColumnMapping Source="Confirmed (Males)" Target="CONFIRMED_MALES" />
        <ColumnMapping Source="Confirmed (Females)" Target="CONFIRMED_FEMALES" />
      </ColumnMappings>
    </LoadTable>
  </Load>
</XmartPipeline>