Category: qlikview

QlikView – Cross Tables ”; Previous Next While analyzing data, we come across situations where we desire columns to become rows and vice-versa. It is not just about transposing, it also involves rolling up many columns together or repeating many values in a row many times to achieve the desired column and row layout in the table. Input data Consider the following input data, which shows region wise sales of a certain product for each quarter. We create a delimited file (CSV) with the below given data. Quarter,Region1,Region2,Region 3 Q1,124,421,471 Q2,415,214,584 Q3,417,321,582 Q4,751,256,95 Loading Input Data We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file. After choosing the options as shown below, click Next. Crosstable Options In the next window (File Wizard → Options), click on the Crosstable button. It highlights the columns in different colors. The pink color shows the qualifier field, which is going to be repeated across many rows for each value of in the Attribute Field. The cell values under the Attribute fields are taken as the data. Click OK. Crosstable Transformation The transformed data appears in which all the Region fields are clubbed to one column but with values repeating for each quarter. Load Script The Load script for the crosstable transformations shows the commands given below. Crosstable Data On creating a Table Box sheet object using the menu Layout → New Sheet Objects → Table Box, we get the following output. Print Page Previous Next Advertisements ”;

QlikView – RangeSum Function ”; Previous Next The RangeSum() function in QlikView is used to do a selective sum on chosen fields which is not easily achieved by the sum function. It can take expressions containing other functions as its arguments and return the sum of those expressions. Input Data Let us consider the monthly sales figure as shown below. Save the data with file name monthly_sales.csv. Month,Sales Volume March,2145 April,2458 May,1245 June,5124 July,7421 August,2584 September,5314 October,7846 November,6532 December,4625 January,8547 February,3265 Load Script 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 from the Data from Files tab and browse for the file containing the above data. Edit the load script to add the following code. Click OK and click Control+R to load the data into QlikView”s memory. LOAD Month, [Sales Volume] FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Applying RangeSum() Function With the above data loaded into QlikView”s memory, we edit the script to add a new column, which will give a rolling sum of the month wise sales volume. For this, we also take the help of the peek function discussed in the earlier chapter to hold the value of the previous record and add it to the sales volume of the current record. The following script achieves the result. LOAD Month, [Sales Volume], rangesum([Sales Volume],peek(”Rolling”)) as Rolling FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above given script. Go to the menu Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below. Print Page Previous Next Advertisements ”;

QlikView – Concatenation ”; Previous Next Concatenation feature in QlikView is used to append the rows from one table to another. It happens even when the tables have different number of columns. It differs from both Join and Keep command, as it does not merge the matching rows from two tables into one row. Input Data Let us consider the following two CSV data files, which are used as input for further illustrations. Please note the second data set has an additional column named Country. SalesRegionOld.csv ProductID,ProductCategory,Region,SaleAmount 1,Outdoor Recreation,Europe,4579 2,Clothing,Europe,4125 3,Costumes & Accessories,South Asia,6521 4,Athletics,South Asia,4125 5,Personal Care,Australia,5124 6,Arts & Entertainment,North AMerica,1245 7,Hardware,South America,456 SalesRegionNew.csv ProductID,ProductCategory,Region,Country,SaleAmount 6,Arts & Entertainment,North AMerica,USA,1245 7,Hardware,South America,Brazil,456 8,Home & Garden,South America,Brazil,241 9,Food,South Asia,Singapore,1247 10,Home & Garden,South Asia,China,5462 11,Office Supplies,Australia,Australia,577 Load Script We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file. Then we edit the commands in the script to apply the concatenation between the tables. Next, we load the above data to QlikView”s memory and create a Table Box by using the menu Layout → New Sheet Objects → Table Box where we choose all the available fields to be displayed as shown below. Concatenated Data Completing above steps we get the Table box displayed as shown below. Please note the duplicate rows for the product ID 6 and 7. Concatenate does not eliminate the duplicates. Print Page Previous Next Advertisements ”;

QlikView – Column Manipulation ”; Previous Next Column Manipulation is a type of Data Transformation in which a new column is populated with values from an existing column, which meets certain criteria. The criteria can be an expression, which is created as part of the Data Transformation step. Input Data Let us consider the following input data, which represents the actual and forecasted sales figures. Month,Forecast,Actual March,2145,2247 April,2458,2125 May,1245,2320 June,5124,3652 July,7421,7514 August,2584,3110 September,5314,4251 October,7846,6354 November,6532,7451 December,4625,1424 January,8547,7852 February,3265,2916 Load Script 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 from the “Data from Files” tab and browse for the file containing the above data. After clicking Next, we choose the Enable Transformation Step button to carry out the required data transformation. Selecting the Data Transformation Choose the Column tab and then choose the New button. It asks to specify the New column and the Row Condition. We specify column 3 as the source column and pick the values, which start with two as the Row Condition. Transformed Data On completing the above steps, we get the transformed data as shown below. Load Script for Transformed Data The load script for the Transformed data can be seen using the script editor. The script shows the expression, which creates the new column with required values. Display Transformed Data The transformed data can be seen by creating a Table Box using the option in the menu Layout → New Sheet Object. Print Page Previous Next Advertisements ”;

QlikView – Rotating Tables ”; Previous Next The Rotating table in QlikView is similar to the column and row transpose feature in Microsoft Excel but with some additional options. We can transpose columns in multiple directions and they give different results. In this chapter, we will be seeing the normal transpose option of converting rows to columns. Input Data Let us consider the following input data, which represents the actual and forecasted sales figures. Month,Forecast,Actual March,2145,2247 April,2458, May,1245, June,5124,3652 July,7421,7514 August,2584, September,5314,4251 October,7846,6354 November,6532,7451 December,4625,1424 January,8547,7852 February,3265, Load Script 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 from the “Data from Files” tab and browse for the file containing the above data. After clicking Next, we choose the Enable Transformation Step button to carry out the required data transformation. Selecting the Data Transformation As we are going to use the Rotate function, let us choose the Rotate tab which displays the values of all the fields. Apply Rotate We click the Transpose button to transpose the above data. The transposed data appears as shown below. Load Script for transformed Data The load script for the Transformed data can be seen using the script editor. The script shows the expression, which replaces the empty cell values. Display Transformed Data The transformed data can be seen by creating a Table Box using the option in the menu Layout → New Sheet Object. Print Page Previous Next Advertisements ”;

QlikView – Pivot Tables ”; Previous Next Pivot Tables are widely used in data analysis to present sum of values across many dimensions available in the data. QlikView”s Chart option has the feature to create a Pivot Table by choosing the appropriate chart type. Input Data Let us consider the following input data, which represents the sales figure of different product lines and product categories. Product_Line,Product_category,Value Sporting Goods,Outdoor Recreation,5642 Food, Beverages & Tobacco,2514 Apparel & Accessories,Clothing,2365 Apparel & Accessories,Costumes & Accessories,4487 Sporting Goods,Athletics,812 Health & Beauty,Personal Care,6912 Arts & Entertainment,Hobbies & Creative Arts,5201 Arts & Entertainment,Paintings,8451 Arts & Entertainment,Musical Instruments,1245 Hardware,Tool Accessories,456 Home & Garden,Bathroom Accessories,241 Food,Drinks,1247 Home & Garden,Lawn & Garden,5462 Office Supplies,Presentation Supplies,577 Hardware,Blocks,548 Baby & Toddler,Diapering,1247 Baby & Toddler,Toys,257 Home & Garden,Pipes,1241 Office Supplies,Display Board,2177 Load Script The above data is loaded to the QlikView’s memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the “Table Files” option from the “Data from Files” tab and browse for the file containing the above data. The following screen appears. Click “OK” and press “Control+R” to load the data into QlikView”s memory. Select Chart Type Next, we use the chart wizard to select the Pivot Table option. Click Next. Select Chart Dimension In the next screen, we choose Product_Line as the first dimension for the chart. Select Chart Expression The next screen prompts us for selecting the chart expression where we choose the sum of value. Select the Chart Format On clicking next, we get the screen to choose chart format in which we select Pyjama Green as the style and the default mode. Pivot Chart Data Completing the above steps gives us the final chart as below. Print Page Previous Next Advertisements ”;

QlikView – Preceding Load ”; Previous Next QlikView Preceding load is a load type in which we use a load statement, which takes the columns of another load statement present in the same script. The data read by the first Load statement, which is at the bottom of the script editor window and then used by the load statements above it. Load Script The below given screen shot shows the script for data, which is loaded as Inline data and then the max function is applied to one of the columns. The load statement at the bottom makes the data available in QlikView”s memory, which is used by the second load statement above the first load statement. The second load statement applies the max function with group by clause. Table Box Data On creating a Table Box Sheet Object, we see the data that is read from the Inline data load option. Print Page Previous Next Advertisements ”;

QlikView – Peek Function ”; Previous Next The peek() function in QlikView is used to fetch the value of a field from a previous record and use it in calculations. Input Data Let us consider the monthly sales figure as shown below. Save the data with file name monthly_sales.csv. Month,Sales Volume March,2145 April,2458 May,1245 June,5124 July,7421 August,2584 September,5314 October,7846 November,6532 December,4625 January,8547 February,3265 Load Script 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 from for the file containing the above data. Edit the load script to add the following code. Click OK and click Control+R to load the data into QlikView”s memory. LOAD Month, [Sales Volume], peek(”Sales Volume”) as Prevmonth FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above script. Go to the menu item Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the csv file in the QlikView Table Box as shown below. Also set the sort order as shown below to get the result in the same order of the field Month as it is in the source. On completing the above steps and clicking Finish, we get the Table box showing the data as given below. Using peek() Function in Calculations The peek() can be used in calculations involving other columns. Let us display the percentage change for sales volume for each month. The following script achieves this result. LOAD Month, [Sales Volume], peek(”Sales Volume”) as Prevvolume, (([Sales Volume]-peek(”Sales Volume”)))/peek(”Sales Volume”)*100 as Difference FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above script. Go to the menu item Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below. Print Page Previous Next Advertisements ”;

QlikView – Functions ”; Previous Next QlikView has many built-in functions, which are available to be applied to data that is already available in memory. These functions are organized into many categories and the syntax of the function appears as soon as it is selected. We can click on the Paste button to get the expression into the editor and supply the arguments. Create Table Box Create a Table Box by following the menu as shown in the screen shot given below. On completing the above given step, we get a window to show the Calculation condition at the bottom left. List of Functions Click on the button next to calculation condition and go to the Function tab. It shows the list of functions available. On choosing String from the functions category, we can see only few functions, which take a string as an argument. In the next chapters, we will see the use of many important functions. Print Page Previous Next Advertisements ”;

QlikView – Excel Files ”; Previous Next QlikView accepts Excel spreadsheet for data analysis by simple drag and drop action. You need to open the QlikView main window and drag and drop the excel file into the interface. It will automatically create the sheet showing the excel data. Select the Excel file Keep the main window of QlikView open and browse for the excel file you want to use. Select a Data Source On dropping the excel file into the main window, the File wizard appears. The File Type is already chosen as Excel. Under Labels, choose Embedded Labels. Click “Next step” to proceed. Load Script The Load script appears which shows the command that loads the data into the QlikView document. This command can be edited. Now, the Excel wizard prompts to save the file in the form of *.qvw file extension. It asks to select a location where you need to save the file. Click “Next step” to proceed. Now it is time to see the data that is loaded from the Excel file. We use a Table Box sheet object to display this data. Create Table Box The Table Box is a sheet object to display the available data as a table. It is invoked from the menu Layout → New Sheet Object → Table Box. On clicking Next, we get the option to choose the fields from the Table Box. You can use the Promote or Demote buttons to rearrange the fields. Table Box Data On completing the above step, the Table Box Sheet Object appears which shows the data that is read from the Excel file. Print Page Previous Next Advertisements ”;