Lockup on City and Segment, version 4.6.6.6.


In the data transformation process from the Excel source to the customer table, when previewing and executing, a specific scenario arises. Two critical pieces of information, segments, and cities, necessitate a lockup procedure. It was observed during the data transformation from the CSV source, which contains states and regions, that encountering repeating rows poses challenges when applying the lockup.


Therefore, it becomes imperative to isolate these rows to enable the lockup process, followed by executing a join between these isolated rows and the ongoing transformation process. The following procedure outlines the necessary steps to achieve this task efficiently. Initially, the user navigates to the Data Container, SQL Server, and proceeds to create two distinct lockup tables - one designated for cities and the other for segments.


The user then confirms that the customer table, referred to as GIN Customer, includes the requisite fields for this process. Upon verifying the field names within the target DB, it is noted that city fields are represented by Code Seed and Desk Seed, while segment fields are labeled as Code Segment and Desk Segment. Consequently, adjustments are made to ensure alignment with the desired nomenclature.


Subsequently, transformations are created for these newly established lockup tables, and their respective naming conventions are standardized. Following this preparatory phase, the focus shifts towards understanding the subsequent actions required. These actions are delineated into three distinct transformation processes, each with a specific objective.


The initial process involves extracting data from the Excel spreadsheet and segregating the city and state information from the address. This process is denoted as Transformation 1. Transformation 2 entails the creation of lockup tables for cities, while Transformation 3 focuses on segment lockup.


It is crucial to note that in order to successfully apply the lockup, it is imperative to ensure that the descriptors do not repeat. Hence, the approach involves isolating the columns containing these descriptors and subsequently applying a distinct operation before proceeding with the lockup.


To operationalize this methodology, Transformation 1 is duplicated, resulting in two distinct transformations - one to isolate the city column and the other to isolate the segment column. These isolated columns are then subjected to distinct operations to eliminate repetitions.


Once isolated and distinct, the lockup process can be initiated. This involves applying the lockup transformation to each set of isolated columns. Upon completion, Transformation 1A contains the city code and descriptor, while Transformation 1B contains the segment code and descriptor.


To consolidate the transformed data, a join operation is performed between Transformation 1A and Transformation 1B, effectively integrating the city and segment information with the original data extracted from the Excel spreadsheet.


This meticulous approach ensures the seamless integration of city and segment data into the customer table, facilitating efficient data management and analysis. The structured methodology employed in this process underscores the commitment to precision and accuracy in data transformation endeavors.