Applying the join between transformations, version 4.6.6.6.


It was observed that the dimension lookup process functions effectively only when applied to a table where the search criterion field is a primary key, meaning its value does not repeat. Consequently, the container had to be partitioned into two transformations.


In the first transformation, the data was selected while removing the columns related to the state. Distinct rows were applied, followed by the addition of the lookup process. Now, the need arises to merge this result with the outcome of the original transformation to achieve the format of the final table, which will have the four highlighted fields shown in the green table.


Hence, the join between the regions transformation and the states and regions transformation will be executed. The primary transformation will be designated accordingly. It is the one that will be continued to work with.


When performing a join between two transformations, it is necessary to designate the primary transformation. Therefore, the transformation that will be the primary one should be clicked. Note that it does not have any programmed transformation methods yet, and the "joinWithOtherObjects" method, which signifies joining with regions, will be selected.


In the "select object" section, the other transformation that is desired to merge, which in this case concerns regions, will be chosen. In the parameters, the fields that are common between the two will be selected. These are the "region" field from the first transformation and the "region" field from the second transformation. Clicking on the plus icon will add this join criterion.


After saving and updating, a preview will be performed. Now, both sets of columns from the first and second transformations are visible. Note the "L" added to the process, indicating "left." This signifies that all fields from the left transformation are joined with all fields from the right transformation.


One of these two columns needs to be eliminated, as the client dimension table does not have two region descriptor fields. The "delete columns" method will be selected to remove the duplicate region column. Either "L region" or "right region" can be chosen.


After updating and previewing, the aliases will be modified so that the column names match those found in the client dimension table.


By selecting the "alias columns" option, the final column names will be included. Considering the client table, the state code and descriptor names are "CodeState" and "DeskState," respectively. The region code and descriptor are "CodeRegion" and "DeskRegion." These will be the names included in the alias transformation.


Once saved and updated, the data from the CSV transformation will be ready to be transported to the table. However, this table alone is not sufficient. The need arises to merge this result with the outcomes of transformations from Excel, where other client-related information is available.


Thank you, and stay tuned for the next video.