 Hello, welcome to SS Unitex. So, we will decide and today we are going to see about the windows transformation in Azure Data Factory. So, what is window transformation? So, the window transformation is where you will define windows based aggregation of a column in your data stream. In the expression builder you can define different type of aggregations and aggregations that could be the sum main max count. So, all these aggregations we can do under the expression builder and that are based on data or the time window function. Inside the SQL Server we have already seen the SQL Overclose. So, this is the same thing inside the ADF as well. So, inside the SQL Server we have seen the functions like lead function, lag function, entail function. So, all these functions we have already seen there. So, under those we are using the Overclose. So, the same thing we could use inside the windows transformation as well. And a new field will be generated in your output that includes these aggregations. You can also include optional group by fruits. So, this is all about the windows transformation. If it is not very clear now, so go to on the scenario where we will see. So, here we can see this is our input stream. So, input stream is having the data for the sales. So, like the first column is the sales order ID, sales order date, item ID, quantity and value. So, these are the input columns. So, here we can see sales order one which is having total six items and the quantity that we could see and the value of those items. Similarly, for ID two we can see and ID three we can see that. So, in the output we just want to have the running total for each of the sales order ID. So, what is the running total? So, if we can see in the output side here then we could see like let us only focus for this one. So, in this we can see ID one. So, the first row which is the value as 2000. So, in the running total that will be 2000 we want. Second row is having 1200. So, in the running total that should be 3200. Then 2000 then that should be 5200. So, we want to have this running total value like this. Once this group is going to change like the sales order is going to change and this time we are having this second sales order and this sales order we can see total six items here as well and once this group is changed then the running total will be reset and going to start from first. So, the first row which is 20000. So, it should be 20000. Then we could see 50000 here. So, it will be 70000. So, we can see the data in the assigning order here for adding the values. Similarly, we can do for the ID 3 as well. So, this is we want to achieve inside the Azure Data Factory. So, for this we have to use the Windows Transformation. So, go to the Azure Data Factory and we will try to see in the practical how we can achieve this from this input. So, here first let me show you the input table. So, this is for Windows 2. So, this is the name of the CSV file. Go to on the edit and under the edit we could see the data. So, we have the same data as we have seen in the Excel file. Now, go to on the Azure Data Factory. Here, let me try to add a new data flow and let me call this data flow as Windows Transformation. Now, here we can add the source. We can click on that and after that we can create a new dataset or we can use the inline. So, I am going to create the new dataset here. So, the source which is the Azure Blob Storage, click on continue, delimited text, click on continue. Then, so name of the dataset. So, it should be the dataset of Windows Function. Now, we can select the link service from here and here the first row as header we can select that one. Then we can browse. Go to the input folder and here we can see the file. So, this is the file we can select and click on OK. Now, we can again click on OK on this. So, your dataset will be created. Now, go to on the projection and under the projection we can see all those columns here. So, go to on the data preview and try to see the data. So, here we can see the data because data flow debug option is on. So, we have already discussed about the data flow debug option. So, here we can see all the data. Now, we need to add the Windows Transformation. So, we can click on this plus symbol and after that we can see in the schema modifier the window. So, we can select that one. So, under the window here we could see the overclose. So, on which column we want to do the overclose. So, first thing we need to take care. So, we want to do the group by on which column. So, here we can see the partition that should be based on the sales order ID. Once the sales order ID will change, then your running total value should be reset. So, that column we have to select in the overclose. So, we can select the sales order ID here. Now, we can go in the sort. So, the sorting we have to select which column by which we want to do the running total. So, we are going to do the running total on the value column. So, let me select that and it should be on the ascending order. After that here we can see the range by see the option and this is all disabled. So, go to the windows column now. So, under the window column it will be going to add a new column. So, what will be the name of that column? So, let me quickly call that as total. And go to the expression. So, what we want to do here? So, we just want to do the sum of value. So, let me try to search for the sum. And after that here we can see the value column. So, we can select and we can save and close. So, it is reflecting an error just because of go to the source and here we can go in the projection and everything is coming under the string. So, we cannot do the sum on the string column. So, actually this is not the string. This is the integer column. So, we can change that and go back to the windows function and here that error has been gone. Now, we can go in the data preview and try to refresh it. So, we will see the data here. So, it is fetching the data now. So, we can wait. So, here in the total we can see the 500. Then the second row is 1000. So, it is the 1500. So, similarly we can see in the second sales order ID the value is 20000. So, that is why it is 20000. And here the second row is 40000. So, it is going to add with the 20000 plus 40000 as 60000. Similarly, we can see the other data. Once sales order ID is going to change. So, your running total is going to reset. So, this is all about the window function. Similarly, if we want to do the aggregate. So, we can also do that. So, let me try to add a new column and this column let me call this as average. And similarly we want to do the average on the value column. So, this is the value column we can select and save and finish. So, go to the data preview and try to refresh it. So, once we will refresh it. So, we will see one more column here with the average. So, that will be having the average of all the running values. So, like here the average which is the 500. So, by default, it should be 500. So, in the second row 500 plus 1000 divided by 2. So, that will be 750. Similarly, if you are going to add 15000 plus 1200. So, that will be 2700 and divided by 3 will be 900. So, this is the average of all the running values. Go back to the window setting and here let me try to add one more column and we will see other options as well. So, let me go here. If we can see here under the function. So, we can go and here let me try to see the function which we can use inside the SQL server. So, those functions may be lag function or the lead function. So, this is the lag function. So, lag function the first is any. So, we have to specify the value. So, we just want to get the value from here. So, go back to the all and here we can see the value. So, we just want to get the value. How many rows before we want? So, that will be one row and after that if there is no any value then we want to see as zero. So, this is something like that. Now, we can save and finish. Go back to here in the data preview and try to refresh it. So, before that if you can see this is the column one. So, under the column one we will be going to see the lag values. So, lag values should be based on the sales order and the value column. So, here we can see the column one. So, before that we did not have anything. So, that is why we have specify zero. So, that is why we can see the zero. On the second we can see the 500. So, that should be 500. On the third row that should be 1500. So, we can see the 1500 here. On the second that will be 1000. So, we can see the 1000 here. Similarly for 1200 then 1200. Once your sales order id value is going to change then it will be reset. So, we can see zero here and after that we can see the 20000 then 40000. So, here we can see 20000 and 40000. Let me put this in one of the sync. So, let me select the sync here and after that here let me use the inline dataset option and after that let me select here the file. So, that file should be delimited file. So, let me select the delimited text here and after that we can select the link service. So, this is the link service we can select. Go to the settings and under the setting we can select the folder path. So, we want to keep the file under the output folder. So, we can select that one and click on okay and after that first row as header that is true and here the file name option should be a single file. So, output file name value will be like window and after that we can publish this. So, once we will publish this it is saying like we have to select. So, go back to the optimize and here the single parties option that we have missed. Now we can publish this. So, it will be going to publish in between let me try to create a new pipeline and on that pipeline this dataflow will be executed. So, go back to here try to add a new pipeline and for executing the dataflow we have to use the dataflow activity. So, this is the dataflow activity we can drag and drop here and under this we can see the settings. So, here we can select the dataflow. So, the dataflow that is the windows transformation this one and now let me try to execute it. So, once we will execute then go to the blob storage and here go to the output folder. So, in the output folder we will see one of the file with the name of window. So, that window file you can see here. So, let me try to open this. So, this will having all the data as we have seen. So, it will have three extra columns like total, average and column one and this is having the same data as we have already seen in the as a data factory. So, this is all about the windows transformation. So, if you have still any doubt then you can comment your questions in the comment box. See you in the next video. Thank you so much.