Category: Big Data & Analytics

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 – 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 – 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 ”;

QlikView – XML File ”; Previous Next XML is a file format, which shares both the file format and the data on the World Wide Web, intranets, and elsewhere using standard ASCII text. It stands for Extensible Markup Language (XML). Similar to HTML it contains markup tags. However, unlike HTML where the markup tag describes structure of the page, in XML the markup tags describe the meaning of the data contained into the file. QlikView can use the data from XML files. The process to load the data from XML files is similar to the loading of delimited files we have seen earlier. Open the script editor. Click on the menu Insert → Load Statement → Load from File. Browse for the XML file you wish to load. In this example, we are choosing the employee_dat.xml file. Select the XML File Structure On opening the selected XML file, a window comes up as shown below. Under the File Type section in the left, choose XML. The content of the XML file now appears as a table along with the header column. Click Finish. File Loader Script The loading of the XML file into QlikView is done through the load script, which can be seen below. So when we use any XML file, we can tweak the below given script to rename the columns or change the file location etc. Now the script wizard prompts you 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 XML 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 ”;