Denormalizing on multiple columns - Cloud - 8.0

Processing (Integration)

Version
Cloud
8.0
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Data Governance > Third-party systems > Processing components (Integration)
Data Quality and Preparation > Third-party systems > Processing components (Integration)
Design and Development > Third-party systems > Processing components (Integration)
Last publication date
2024-02-20

Procedure

  1. Drop the following components: tFileInputDelimited, tDenormalize, tLogRow from the Palette to the design workspace.
  2. Connect all components using a Row main connection.
  3. On the tFileInputDelimited Basic settings panel, set the filepath to the file to be denormalized.
  4. Define the Row and Field separators, the Header and other information if required.
  5. The file schema is made of four columns including: Name, FirstName, HomeCity, WorkCity.
    Name;FirstName;HomeCity;WorkCity
    Pitt;Brad;Berverly Hills;Los Angeles
    Pitt;Brad;Paris;London
    Joli;Angelina;Berlin;Berlin
    Joli;Angelina;Berlin;Los Angeles
    Joli;Angelina;Los Angeles;Los Angeles
    Willis;Bruce;Paris;Los Angeles
    Willis;Bruce;Paris;Madrid
    Willis;Bruce;Madrid;Paris
    Willis;Bruce;Roma;Dublin
    Moore;Demi;New York;Paris
    Moore;Demi;Rio de Janeiro;Los Angeles
  6. In the tDenormalize component Basic settings, select the columns that contain the repetition.
    These are the column which are meant to occur multiple times in the document. In this example, FirstName, HomeCity and WorkCity are the columns against which the denormalization is performed.
  7. Click the to add rows and define the columns to denormalize.
  8. In the Delimiter column, define the separator between double quotes, to split concatenated values.
  9. Save your Job and press F6 to execute it.
    The denormalized values are concatenated using a semicolon.
    |=-----+--------------------------+-------------------------+------------------------------=|
    |Name  |FirstName                 |HomeCity                 |WorkCity                       |
    |=-----+--------------------------+-------------------------+------------------------------=|
    |Joli  |Angelina;Angelina;Angelina|Berlin;Berlin;Los Angeles|Berlin;Los Angeles;Los Angeles |
    |Moore |Demi;Demi                 |New York;Rio de Janeiro  |Paris;Los Angeles              |
    |Willis|Bruce;Bruce;Bruce;Bruce   |Paris;Paris;Madrid;Roma  |Los Angeles;Madrid;Paris;Dublin|
    |Pitt  |Brad;Brad                 |Berverly Hills;Paris     |Los Angeles;London             |
    '------+--------------------------+-------------------------+-------------------------------'
  10. To delete the duplicate occurrences, open the Basic settings of tDenormalize.
  11. Select the Merge same value check box.
  12. Save your Job again and press F6 to execute it.

Results

The duplicate occurrences are merged.
|=-----+---------+-----------------------+------------------------------=|
|Name  |FirstName|HomeCity               |WorkCity                       |
|=-----+---------+-----------------------+------------------------------=|
|Joli  |Angelina |Berlin;Los Angeles     |Berlin;Los Angeles             |
|Moore |Demi     |New York;Rio de Janeiro|Paris;Los Angeles              |
|Willis|Bruce    |Paris;Madrid;Roma      |Los Angeles;Madrid;Paris;Dublin|
|Pitt  |Brad     |Berverly Hills;Paris   |Los Angeles;London             |
'------+---------+-----------------------+-------------------------------'