QlikView - Data Transformation


Data Transformation is the process of modifying the existing data to a new data format. It can also involve filtering out or adding some specific values to the existing data set. QlikView can carry out data transformation after reading it to its memory and using many in-built functions.

Input Data

Let us consider the following input data, which represents the sales figures of each month. This is stored as a csv file with name quarterly_sales.csv

Month,SalesVolume
March,2145
April,2458
May,1245
Sales Values in Q2
June,5124
July,7421
August,2584
Sales Values in Q3
September,5314
October,7846
November,6532
December,4625
January,8547
February,3265

Loading the Data

The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the "Table Files" option form the "Data from Files" tab and browse for the file quarterlt_sales.csv. Click next.

1_dt_file_wizard

The Transformation Wizard

The next screen prompts us to choose some data transformation. Click on the button Enable Transformation Step.

2_dt_edit_transformations

Transformation Type

In this step, we will select the transformation to eliminate the rows, which describe the quarter. We select Garbage → delete marked and select the two rows, which are not required. Click Next.

3_dt_garbage_rows

File Wizard Options

After selecting the type of Transformation and the rows to be removed, the next screen prompts us for any further transformation like selecting a where clause or adding any Prefixes. We will ignore this step and click Finish.

4_dt_next_garbage_rows

Load Script for Transformed Data

The Load script for the above data after all the transformation steps are complete is given below.

5_dt_load_script

Displaying Transformed Data

The transformed data can be displayed by using a Table Box sheet object. The steps to create it are given below.

6_dt_create_table_box

Next, we choose the fields for the Table Box.

7_dt_table_box_fields

The Table Box now displays the data in the sheet.

8_dt_fina_data
Advertisements