Simple mapping and tMap time savers

This recipe will illustrate the most basic mapping options within the tMap component and some of the column level tricks that can be used to speed up mapping by removing large amounts of repetitive actions.

Getting ready

Open the job jo_cook_ch04_0010_basicMapping.

How to do it...

  1. Drag a tMap component from the right-hand panel.
  2. Connect the tFileInputDelimited component to tMap.
  3. Connect the output, name it as outputCustomer and accept the schema of the target component.
  4. Open tMap and you will notice that the inputs and outputs are named the same as the flows.

    Rename the flows

  5. Close tMap and left-click the input flow so that row1 is highlighted. Take a short pause; click again on the row1 text and the text will be editable. Rename the flow to customer.
  6. Open tMap and you will see that the names of the tMap input table have now changed to match the row name of the input flow.

    Manually dragging columns

  7. Click the left mouse button on dateOfBirth and drag to dateOfBirth expression in the output. This is the most basic method of copying data from input to output.

    Create new columns by dragging

  8. Press Ctrl and left mouse click the annualTotal and prevYearTotal columns.
  9. Release Ctrl and left mouse click annualTotal.
  10. Holding down the left mouse button, drag the columns to the very bottom of the output table.
  11. Do not release the left mouse button until you see a blue line at the end of the table and an information box that states Insert all selected entries.
    How to do it...
  12. You will see that the new columns have been added to the output, and their values automatically mapped from the input.

    Reposition a column

  13. Use the up and down arrows in the schema tab to move the new columns to the positions below dateOfBirth.

    Deleting a column

  14. Highlight totalTwoYears in the output table and then click the X button to delete it.

    Adding a column manually

  15. In the Schema editor for the output, left mouse click the name field, then click the + button to create a new column.
  16. Change the name of newColumn to postcode.

    Automapping same named columns

  17. Click the output table and then click Auto map!, as shown in the following screenshot:
    How to do it...
  18. You should notice that all the columns that share the same input and output name have been mapped automatically.

    Concatenating input columns

  19. Highlight the firstName, middleName, and lastName input columns by holding down Ctrl and left mouse clicking each one individually.
  20. Drag them across to the output name column but do not release the left mouse button yet. While hovering over the name column, notice the floating information box. This tells us that the mode is append.
  21. Release the left mouse button and notice that the three columns have been copied to the same expression.
  22. Repeat steps 10 to 12, and you will see that the columns have been appended yet again. Append is the default mode when dragging and dropping.

    Overwriting columns

  23. Repeat steps 10 to 12, however before releasing the left mouse button hold down Ctrl. You will see that the information box changes to Overwrite mode.
  24. Release the left mouse button, and you will see that the expression containing six fields has been overwritten by three.
  25. Add +" "+ between each of the columns to complete the expression.

    Copy columns by position

  26. Highlight the payment1 column.
  27. Hold the Shift key and click payment4.
  28. Release the left mouse button, select the payment1 column and drag the group across to the lastFourPayments1 column in the output.
  29. Drag the four columns to the output, but do not release the mouse button. Notice that the floating box says that this is the append mode.
  30. Press Ctrl and you will see that the mode changes to overwrite mode
  31. Press Shift and the mode changes to each source entry to each target expression.
  32. This is the mode we want, so release the left mouse button. You will see that the columns have been mapped individually. Note that the copy of multiple columns by position can also be performed with non-sequential columns selected using Ctrl and left mouse click.
  33. Your tMap should now look like the following and you can run the job.
    How to do it...

How it works...

tMap enables columns to be mapped, ignored, and added to the output very easily to ensure that the correct results are produced.

Shortcuts such as Auto map! and the group copies, enable many rows to be copied at once, saving time and effort.

New columns can be created by dragging and dropping from the input, as well as via the schema editor.

There's more…

In this recipe there is just a single input and output, so recognizing the source column in an expression is simple. When multiple inputs and outputs are used, then this is no longer true, unless flows are named sensibly. This is why we have included the renaming of the flows in this, the most basic recipe.

Tip

Always name the inputs to tMap. Often, tMaps have multiple inputs and outputs. Ensuring that the flow names are consistent helps in identifying from where a data element has been sourced, which will make debugging easier.

上一章目录下一章