Transforming the product list. Version 4.6.6.6. Above, we have the product descriptor. It is formed by three parts.


The first part is the brand, the second the size, and the third part is the flavor. We need to apply the transformation that will split these three parts into three different columns, each with one of these values. We have the hyphen as the parameter, which separates the values to be extracted.


Those familiar with programming languages know that the split function transforms text into an array. So, the 0th element of the array will be the brand, the 1st element will be the size, and the 2nd element will be the flavor. Let's see how to apply this in PIT.


Let's now create a transformation associated with the "products" dimension container. To do this, click on the toolbox of the container, then on "Transformations". Clicking on "Transformations" and then click on "Refresh", we have the transformation created here.


Let's adapt its name to the pattern we're using. Let's click "Update". And now we have a transformation using the naming pattern we're using.


However, we now need to create the transformations. And our first objective is to separate the product name that is in the "Description" column of Excel into three parts: the brand, the size, and the flavor. To do this, let's select "New Column".


The "New Column" transformation will create a new column. So let's put the creation of the brand column as the descriptor of this transformation. We know that the brand column is the first element of the descriptor.


I'll click on "Objects", and note that when I click on "Save", we have here a series of functions already prepared within the PIS. There are numeric functions, date functions, string functions, processing functions, etc., several functions already prepared. One of them, let's look for here, is the "Split String" function.


The "Split String" function splits a string into an array based on a delimiter, and I can select the position of this array. Let's select this "Split String" function. Let's click "Save", and in the Parameters, we will determine the name of the column.


Let's select "Brand". The column type will be a string. We need to put the parameters of this function.


In the case of "Split String", we select three parameters. The first parameter, we have to put a name. This name in the Parameters column can be any text, but it's important that you put this text.


This first parameter will be the column that contains the string to be split. In this case, it's the "Description" column. The second parameter is the delimiter.


The delimiter will be the hyphen, but here, as it is a constant parameter, we select the "Type Value" option, and in Value, we type the hyphen. Pay attention, there's a very subtle tip here. In fact, we won't select the hyphen.


We will select a space, hyphen, space. Pay close attention. Space, hyphen, space.


Let's see the result and later, I'll explain why I put this space, hyphen, space. I'll press Enter. And the third parameter will be the position, which I'll also type manually.


We will use position 0. Always remember to press Enter whenever you enter some information into a grid, within the piece, because if you don't click Enter and save, that information won't be recorded. I'll click Save here, and I'll click Update.


Let's present a Preview now. Note what we have. We have a "brand" column, which contains the first element of the array. Let's move on to the second column, which represents the size.


We don't need to recreate the entire configuration. Just select the row and click this button to duplicate the transformation. Clicking on the second transformation and clicking on Update.


Let's now replace it with "Size". In parameters, the name of the column we'll call "Size", and we'll simply change the string position to position 1. Let's click Save and now duplicate the second line to a third line. Let's modify it.


Let's insert "Flavor" here, and the position will be position 2, and the column name will be "Flavor". Let's click Save, Update, and let's see the result of these three transformations. So, notice that we now have a brand column, a size column, and a flavor column, which have already been separated from the descriptor.


Now, let's go back to a point I mentioned earlier, which was about the delimiter. We selected a space, hyphen, space. If you notice here, what really separates the end of one text from the beginning of the next text is not the hyphen, it's a space, hyphen, space.


What happens if I only say that the delimiter is a hyphen? When we split the data, for example, in this line I'm using as an example, we would have a blank space after the letter "O", and before the next element, we would have a blank space before "set". And this could cause problems later, mainly because in this brand column, we will use the lockup process later on. And the lockup process consists of searching for a certain code in another table based on the descriptor.


And when I do this, if I don't separate the blank space before and after the hyphen, the text that would be used to search for the code in the lockup table would not be, for example, in this highlighted case, "videira do campo", but it would be "space videira do campo". And then it wouldn't find this element. That's why the delimiter should be set as space, hyphen, space.


Let's continue. So, we have the three columns, let's now determine the lockup. And the lockup will be a lockup not of dimension because I'm not creating the code for the element, but a lockup from the fact, where I'll just search for a code that has already been created.


And I'll apply the lockup transformation table of dimension brand. So let's go. I'll select the brand lockup here. I'll put the brand lockup. We'll select in objects. Click Save.


Let's select the lockup table, which is the lockup transformation table of dimension brand. In parameters, we'll apply the lockup factable. Field to be used for search.


The brand field, which is what we obtained there through the split. The search field is the brand name, and the code to be obtained is the brand code.


We'll put a default value, for example, 1, if the code is not found. Let's click Save. Let's click Update.


And now, the preview. Note what I have here are the brand codes. It's time to delete the columns that are not important for transferring the data to the product table, since we will use the automatic box.


Looking at the destination database, we can see that the existing fields are the product code and its descriptor, the attributes, and only the brand code. So, let's go back to transformations. Let's create one more transformation, called "delete rows", which we'll call "delete columns".


And we'll delete only the brand column because I don't need the brand descriptor in the table, I only need its code. I'll press Enter, Save, Update, OK. Let's give a preview now.


Done, we now have only the necessary columns. The next step is to add the names through the alias. So, in transformations, let's select "alias columns", include the column name.


SKU will be replaced by "code", the description, let's check here, it's the "Desk Product", the size, let's check here, it's the "ATT Size", the flavor, "ATT Flavor", and the brand, "Brand Code". I'll press Enter to confirm the data entry, Save, click Update, and now let's click on preview.


Done, we have the source table ready to be saved in the destination. What we need to do is modify the metadata. So, let's go here to the "Brand Code" column, change the type to String, the primary key is the product code, and the sizes.


We have 50 for the code, let's check here, the size. The code is 50, the descriptor and the attributes are 200, and the brand code size is 50. 50, 200, 200, 250. I'll click Save, let's click Update, and done.


A transformation is already ready for your data to be transferred to the destination table. Thank you, and until the next video.