Obtaining the city and state, version 4.6.6.6.


In the previous video, the transformations from the CSV source were already addressed. Now, attention will be turned to the Excel source. To proceed, click on the Excel Container, and examine the resulting spreadsheet data.


A transformation will be created for this container. Click on Transformations, then OK. Returning to the Transformations tab, perform a refresh.


The transformation is now visible. To maintain naming consistency, it will be renamed accordingly. Update the name, and then preview the transformation. As expected, the preview result matches that of the container since no transformations have been applied yet.


Recalling the existing columns for client code and descriptor, as well as the segment descriptor, a lookup process needs to be applied. To obtain the city and state, extraction from the address text is required. Notably, a separator, namely the dash, distinguishes the address from the city and state.


This technique was previously implemented during the product transformation process. The splitString function will be utilized once more. The city will be obtained from position 1 and the state from position 2.


Begin by creating a new column transformation for city extraction. Save the object, select the splitString function, and define parameters accordingly. Create a column named "city" of type string. Specify the address column as the first parameter, followed by the separator (space), and then position 1. Save and update.


Duplicate this transformation for the state extraction. Update the transformation name to "state" and set the position parameter to 2. Save and update. Execute the preview to observe the city and state columns.


However, discrepancies are noted where the state column appears empty. This anomaly occurs because the separator varies across entries, such as a comma instead of a dash. Additionally, irregularities exist where extra spaces disrupt the split function's efficacy.


In such cases, a custom transformation function is required. Fortunately, PIS provides a platform to construct specialized functions. Navigate to Data Containers and select the "functions in process" option to access this feature.


In the next video, the logic for handling these anomalies and constructing a custom function within PIS will be explored.


Thank you, and stay tuned for the next video.