Transformation Wizard in Qlikview – 3 Unique Features
In the last tutorial, we learned about the QlikView Transformation wizard. Now, in this tutorial, we are going to learn about the other three features of Transformation Wizard in Qlikview. These three features are Fill Function, Column Manipulation and Rotating Tables. All of them serve different functions in data transformation.
So, let’s start the tutorial “Features of Transformation Wizard in Qlikview”.
2. Features of Transformation Wizard in Qlikview
There are 3 features of Transformation Wizard in Qlikview.
i. QlikView Fill Function
Fill function use when you want to fill in values from already existing fields. Let us understand this by applying the fill function to actual sales dataset. We will load the data file into Qlikview’s memory.
Select the data file named ‘Salesvol’. This is a monthly data record of the sales volume along with an incomplete column of the production volume.
Click on NEXT and enable the transformation wizard.
Select the FILL tab. You will see the data loaded.
Now, if you notice the left out columns in the Production Volume field, are the ones where the sales were equal to the production done. So, in this case, we need to do is fill the empty columns with the values in the corresponding month’s sales volume column. To do this, click on FILL option. Select the Target Column. Choose Fill Type. And click on Cell Condition if you wish to make any changes. Click on OK.
As you can see, the empty sections are filled with values.
Click on NEXT.
Click FINISH. The new script will be loaded automatically.
LOAD Month, SalesVolume, [Production volumne] FROM [C:\Users\admin\Desktop\Dataflair\Salesvol.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(Replace (3, left, StrCnd(null)) ));
Save it and click OK. Create a table box to see the transformed dataset.
ii. QlikView Column Manipulation
Through Column Manipulation, you can create a new column and enter values in it from a preexisting column. Although this happens base on a criterion which you set, it is an expression or a function. In our file of Sales volume, we wanted to add a new column and repeat the months’ list next to the production volume column. For this, we use a column manipulation feature.
Enable the transformation wizard and go to the COLUMN tab. To do the changes click on NEW…
Select the Source Column (we have selected 1 because we want the Month field). Now, click on ‘Cells these rows’ to apply a condition on the rows. We have chosen RANGE condition and selected the start value of the range.
Next, select the end value of the range from TO option.
Once this is done, click on AND to display the expression code.
Click on OK.
We have the column added with months list in it.
Click on NEXT. And then click FINISH.
The new script will be automatically loaded in the script editor.
LOAD Month, SalesVolume, [Production volumne], Monthl FROM [C:\Users\admin\Desktop\Dataflair\Salesvol.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(Replace (3, left, StrCnd(null)), ColXtr(1, RowCnd(Interval, Pos(Top, 1), Pos(Top, 13), Select(1,0)), 0) ));
Save the script. Close the script editor by clicking OK and create a table box after reloading the script with CTRL+R.
The new column has been added to the table using column manipulation.
iii. QlikView Rotating Tables
The Rotating Tables tab in the transformation wizard changes the orientation of the table. It transposes the table which means rows and columns switch places. You can change the orientation of the table in several other ways as well by using this feature.
In order to rotate or transpose a table. Enable the Transformation Wizard and select the ROTATE tab.
Click on the TRANSPOSE option. The table will be rotated.
Click on NEXT and then FINISH. The script will be loaded
LOAD Month, January, February, March, April, May, June, July, August, September, October, November, December FROM [C:\Users\admin\Desktop\Dataflair\Salesvol.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, filters(Replace(3,left, StrCnd(null))), Transpose () ));
Save the script. Reload it and create a new table box object. Select all the fields and the final rotated table will be displayed.
So, this was all about Features of Transformation Wizard in Qlikview. Hope you like our explanation.
This was all about the ways you can transform your data in. All features of Transformation Wizard in Qlikview are a part of the Transformation process of the ETL process (Extract, Transform, Load). Fill, column and rotate functions are given to save user’s time and ease up working with huge amounts of data. Furthermore, if you have any query, feel free to ask in the comment box.
Related Topic – QlikView Ports