Procedure
-
Drop the following components: tFileInputDelimited, tDenormalize, tLogRow
from the Palette to the design
workspace.
-
Connect all components using a Row
main connection.
-
On the tFileInputDelimited
Basic settings panel, set the filepath to
the file to be denormalized.
-
Define the Row and
Field
separators, the Header and other information if required.
-
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
-
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.
-
Click the
to add rows and define the columns to denormalize.
-
In the Delimiter column,
define the separator between double quotes, to split concatenated values.
-
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 |
'------+--------------------------+-------------------------+-------------------------------'
-
To delete the duplicate occurrences, open the Basic
settings of tDenormalize.
-
Select the Merge same value check
box.
-
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 |
'------+---------+-----------------------+-------------------------------'
