Implementing the transformation function code, version 4.6.6.6.


Let's begin by initializing the input parameters of the function to obtain the city and state. Firstly, create a variable representing the zeroth parameter, which will contain the address from the "Androids" column of the Excel spreadsheet. Copy this line and paste it below, designating the second parameter as the type parameter. This parameter will determine whether to extract a city or a state. These two parameters, "Androids" and "type," will be passed from the transformation into the function.


Next, determine the scenario in which the function operates: whether the separator between the city and state is a dash, comma, or any other character. To achieve this, let's consider the position of the character four positions from the right end of the string. This character will indicate whether we're dealing with case 1, case 2, or case 3.


Now, let's delve into a simple example to understand this concept better. Imagine a string "ABCDEFGHIJ." In .NET programming, string indexing starts at 0. Therefore, A is at position 0, B is at position 1, and so forth. The length of the string is 10 characters. If we were to use the substring function, specifying "3" and "2" would retrieve the characters D and E, starting from position 3. Similarly, "5" and "3" would yield F, G, and H.


Now, consider the variable "size." To obtain the character we need for testing, i.e., the fourth character from the right end, we can use the substring function as follows: "size - 4" to start at the appropriate position and retrieve one character. This character will indicate whether we're in case 1, case 2, or case 3.


Returning to the code, declare a variable named "size" to represent the string length. Use the "LAMBD" function to obtain the string's length. Then, define another variable, "charTest," to store the character we'll use for testing. This character will be retrieved using the "substring" function, starting from "size - 4" and fetching one character.


For testing purposes, temporarily add a "RETURN charTest" statement. Although this isn't the final function, it allows us to verify whether the character retrieval is successful. Click "UPDATE" to save the function in the PIS metadata database.


Now, let's test the function using the test screen. Provide two parameters: the address string and the type (for now, we'll use "seed" as a placeholder). Test with addresses containing a dash, comma, and a space to cover all scenarios. Check whether the character returned matches the expected separator. If successful, proceed with implementing the logic to handle each case accordingly.


In the next video, we'll continue developing the transformation function. Thank you, and see you in the next video.