Category: qlikview

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 – Bar Chart ”; Previous Next Bar charts are very widely used charting method to study the relation between two dimensions in form of bars. The height of the bar in the graph represents the value of one dimension. The number of bars represent the sequence of values or grouped values of another dimension. 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 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 containing the above data. Edit the load script to add the following code. Click “OK” and press “Control+R” to load the data into the QlikView”s memory. LOAD Product_Line, Product_category, Value FROM [C:Qlikviewdataproduct_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Table Box(Sheet Object) For the above data, let us create a Table Box, which will show the data in a tabular form. Go to the menu Layout → New Sheet Object → Table Box and choose the column as shown below. Click Apply and then OK to finish creating the Table box. The below given screen appears. Using the Quick Chart Wizard To start creating a bar chart, we will use the quick chart wizard. On clicking it, the following screen appears which prompts for selecting the chart type. Choose bar Chart and click Next. Choose the Chart Dimension Choose Product Line as the First Dimension. Choose the Chart Expression The chart expression is used to apply the functions like Sum, Average, or Count on the fields with numeric values. We will apply the Sum function on the filed named Value. Click Next. Choose the Chart Format The Chart format defines the style and orientation of the chart. We choose the first option in each category. Click Next. The Bar Chart The Bar chart appears as shown below. It shows the height of the field value for different product lines. Print Page Previous Next Advertisements ”;

QlikView – Documents ”; Previous Next QlikView documents are the files that contain all the objects used for the data presentation and analysis. It contains the sheets, variables, data model, source-data connection details, and even the data that is loaded after pulling it from the source. Document Properties We can quickly find out the basic information of a QlikView document. Click on Help → document Support Info. Given below is a sample output. Setting Document Background Image We can set an image as the background image for a document using the check box Wallpaper Image check box under the General tab. We choose an image and align it at the left top position using the dropdown buttons. The following screen appears on selecting the above options. Sheet Objects The QlikView document contains various Sheet objects, which can be moved around by dragging them and placed anywhere in the document. Let us create two sheet objects, a Table box and a Statistics Box. You can follow the earlier chapters where we have already learnt to create sheet objects. In addition, we are using the file Product_sales.csv, which is mentioned here. Details of the Sheets objects can be seen using the “Sheets” tab. It shows all the sheets contained in the document and for each sheet, the sheet objects are shown. Both the sheets and sheet objects have unique IDs. We can also edit various properties of these objects from this tab itself. Scheduling a Document A QlikView document can be scheduled to refresh at some desired intervals. This is done using the Schedule tab available under the Document properties window. Print Page Previous Next Advertisements ”;

QlikView – Circular Reference ”; Previous Next Circular Reference occurs when we can traverse from one table to another using two or more different paths. This means you can join Table1 with Table2 directly using a column or you can also first join Table1 with Table3 and then table3 with Table2. This can lead to incorrect result in the output formed by a data model, which loads all these three tables. QlikView prevents the load of such data into its memory once it recognizes a circular reference. Input Data Let us consider the following three CSV data files, which are used as input for further illustrations. SalesCountries: ProductID,ProductCategory,Country,SaleAmount 1,Outdoor Recreation,Italy,4579 2,Clothing,USA,4125 3,Costumes & Accessories,South Korea,6521 4,Athletics,Japan,4125 5,Personal Care,Brazil,5124 6,Arts & Entertainment,China,1245 7,Hardware,South America,456 8,Home & Garden,Peru,241 9,Food,India,1247 10,Home & Garden,Singapore,5462 11,Office Supplies,Hungary,577 ProductCountry: ProductID, Country 3,Brazil 3,China 2,Korea 1,USA 2,Singapore 7,Sri Lanka 1,Italy 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. Data Load After creating the above script, we load the data to QlikView”s memory using the command Control+R. This is when we get the error prompt mentioning the presence of circular loop in the tables getting loaded. Data Model To find the exact cause of the above warning we can look at the data model by using the menu command for table viewer – Control+T. The following screen comes up, which clearly shows the circular reference. Here the join between RegionCountry and SalesRegion can be directly achieved using the field Region. It can also be achieved by first going to the table ProductCountry, using the field Country and then mapping ProdcutID with Salesregion. Resolving Circular Reference The above circular reference can be resolved by renaming some of the columns in the data sets so that QlikView does not form an association between the tables automatically using the column names. For this, we will rename country column in RegionCountry to SalesCountry. In the data set ProdcuCountry, we rename the Country column to ProductCountry. Rectified Data Model The Rectified data model after renaming the column above can be seen using the command Control+T. Now we can see that the relationship between the tables does not form a loop. Pressing Control+R to reload the data does not give us the warning anymore and we can use this data to create reports. Print Page Previous Next Advertisements ”;

QlikView – Text Object ”; Previous Next QlikView text Object is used to show some descriptive information about the QlikView report being displayed. It can also show calculations based on certain expressions. It is mainly used for displaying nicely formatted information using colors and different font types in a box separately from the other Sheet Objects. 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 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 press “Control+R” to load the data into the QlikView”s memory. LOAD Product_Line, Product_category, Value FROM [C:Qlikviewdataproduct_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Table Box For the above data, let us create a Table Box , which will show the data in a tabular form. Go to the menu Layout → New Sheet Object → Table Box and choose the column as shown below. Click Apply and then OK to finish creating the Table box. The following screen appears. Creating Text Object For the above data, let us create a Text Object. Go to the menu Layout → New Sheet Object → Text Object as shown below. Creating Text Object Expression On the text box created above, right click and choose properties. Then enter the content to be displayed on the Text Object in the Text box under the General tab as shown below. Choosing the Background Color The background color of the Text Object can be set using the background option in the General tab. The Text Object The final Text Object is shown below. If we click on some Product Line to filter it, then the content in the Text Object changes accordingly to reflect the new values. 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 ”;