Category: Big Data & Analytics

QlikView – Architecture ”; Previous Next As a leading Business Discovery Platform, QlikView is built with a very different approach to data discovery than other traditional platforms. QlikView does not first build a query and then fetch the result based on the query. Rather, it forms associations between different data objects as soon as it is loaded and prompts the user to explore the data in any way. There is no predefined data drill down paths. The data drill down paths can happen in any direction as long as the data is available and associated. Of course, a user can also play a role in creating the associations between data elements using data modeling approach available in QlikView. Architectural Overview QlikView”s architecture consists of a front end to visualize the processed data and a back end to provide the security and publication mechanism for QlikView user documents. The diagram given below depicts the internal working of QlikView. The architecture is discussed in detail below the picture. Front End The Front end in QlikView is a browser-based access point for viewing the QlikView documents. It contains the QlikView Server, which is mainly used by the Business users to access the already created BI reports through an internet or intranet URL. Business users explore and interact with data using this front end and derive conclusions about the data. They also collaborate with other users on a given set of reports by sharing insights and exploring data together, in real time or off-line. These user documents are in the format .qvw, which can also be stored in the windows OS as a standalone document The QlikView server in the front end manages the client server communication between the user and QlikView backend system. Back End The QlikView backend consists of QlikView desktop and QlikView publisher. The QlikView desktop is a wizard-driven Windows environment, which has the features to load and transform data from its source. Its drag and drop feature is used to create the GUI layout of the reports that becomes visible in the frontend. The file types, which are created by QlikView desktop are stored with an extension of .qvw. These are the files that are passed on to the QlikView server in the front end, which serves the users with these files. Also .qvw files can be modified to store the data-inly files, which are known as .qvd files. They are binary files, which contain only the data and not the GUI components. The QlikView publisher is used as distribution service to distribute the .qvw documents among various QlikView servers and users. It handles the authorization and access privileges. It also does the direct loading of data from data sources by using the connection strings defined in the .qvw files. Print Page Previous Next Advertisements ”;

QlikView – Aggregate Functions ”; Previous Next QlikView Aggregate functions are used to produce aggregate data from the rows of the table. The functions are applied to the columns when creating the load script. Given below is a sample list of Aggregate functions. We also need to apply the Group by clause appropriately when applying the aggregate functions. SUM gives the sum of the numeric values of the column. AVG gives the average of the numeric values of the column. MAX gives the maximum of the numeric values of the column. MIN gives the minimum of the numeric values of the column. Example Consider the following data stored as product_sales.csv in the local system. It represents the sales figures for different product lines and product category in a store. Product_Line,Product_category,Quantity,Value Sporting Goods,Outdoor Recreation,12,5642 Food, Beverages & Tobacco,38,2514 Apparel & Accessories,Clothing,54,2365 Apparel & Accessories,Costumes & Accessories,29,4487 Sporting Goods,Athletics,11,812 Health & Beauty,Personal Care,21,6912 Arts & Entertainment,Hobbies & Creative Arts,58,5201 Arts & Entertainment,Paintings,73,8451 Arts & Entertainment,Musical Instruments,41,1245 Hardware,Tool Accessories,2,456 Home & Garden,Bathroom Accessories,36,241 Food,Drinks,54,1247 Home & Garden,Lawn & Garden,29,5462 Office Supplies,Presentation Supplies,22,577 Hardware,Blocks,53,548 Baby & Toddler,Diapering,19,1247 Creating the Load Script We open the script editor in a new QlikView document using Control+E. The following code creates the required tables as inline data. After creating this script press control+R to reload the data into the QlikView document. Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the Aggregate function. Go to the menu Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and the select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below. Applying SUM() function Given below is the load script to find the sum of the sales quantity and sales value across the Product Lines and product categories. Click OK and press Control+R to reload the data into QlikView document. Now follow the same steps as given above in − Creating Sheet Objects to create a QlikView Table Box for displaying the result of the script as shown below. Applying AVG() function Given below is the load script to create the average of the sales quantity and sales value across each Product Line. # Average sales of Quantity and value in each Product Line. LOAD Product_Line, avg(Quantity), avg(Value) FROM [E:Qlikviewdataproduct_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq) Group by Product_Line; Click OK and press Control+R to reload the data into QlikView document. Now follow the same steps as given above in − Creating Sheet Objects to create a QlikView Table Box for displaying the result of the script as shown below. Applying MAX() & MIN() function Given below is the load script to create the maximum and minimum of the sales quantity across each Product Line. # Maximum and Minimum sales in each product Line. LOAD Product_Line, max(Quantity) as MaxQuantity, min(Quantity) as MinQuantity FROM [E:Qlikviewdataproduct_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq) Group by Product_Line; Click OK and Control+R to reload the data into QlikView document. Now follow the same steps as above in − Creating Sheet Objects to create a QlikView Table Box for displaying the result of the script as shown below. Print Page Previous Next Advertisements ”;

QlikView – Navigation ”; Previous Next In this chapter, we will get acquainted with the screens available to a user for doing various operations. We will learn the basic navigation and know the different functions performed by the icons available in QlikView. Getting Started Screen This screen is a gentle introduction to navigate around QlikView. This screen comes up when you start QlikView and keep the Show start page when launching QlikView option checked. If you scroll down the examples section in the left, you can click any of the examples like − Movies Database, Data Visualization etc. to invoke it and see how QlikView works. Feel free to click around! On moving to the right, you notice the Recent and Favourites link, which show all the recently visited QlikView documents and the documents you want to visit frequently. Menu Commands On closing the ‘Getting Started’ window, we get the main interface with all the available Menu commands. They represent the entire set of features available in QlikView. Given below is an overview of each section of the Menu Commands. File Menu This menu is used to create a new QlikView file and open the existing files from both local system and QlikView server. The important features in this menu are − Mail as attachment to email the currently open document as an attachment. Reduce Data to view only the report layout and database structure without any data. Table viewer option is used to see the structure of the tables, fields and their association in a graphical view. Edit Menu This menu is used to carry out the editing options like copy, paste, cut and using format painter. The important features in this menu are − Active All option activates all the sheet objects present in the opened sheet. Removeto remove a sheet from the active window. Advanced search option is used to do a search with advanced search expressions using multi box. View Menu This menu is used to view the standard toolbars and zoom in/ zoom out features. It also displays all the active sheets as a cascade menu. The important features in this menu are − Turn on/off WebView mode toggles the WebView mode and local view mode. Current Selections displays the field name and file values of the selected sheet objects. Design Grid is used to toggle the sheet object placeholders for active object(s) and snap-to-grid for sizing and moving objects in the layout. Selection Menu This menu is used to select and clear the selection of values in the sheet objects. It also provides the feature of going back and forward into different logical statements of the sheet, you are working on. The important features in this menu are − Lock locks all the values in current selection. Unlock unlocks all the locked values in the current selection. Layout Menu Layout Menu is used to add tabbed sheets, select different sheets and rearrange sheet objects. The important features in this menu are − Promote sheet moves the current sheet or tab one step forward. Demote sheet moves the current sheet or tab one step backward. Delete sheet deletes the active sheet and everything in it. Settings Menu Settings menu is used to set the user preferences, document properties, and sheet properties. The important features in this menu are − Variable overview all the non-hidden variables and their values in a single list. Expression Overview shows expressions from the document, sheet and sheet objects as a single list. Bookmark Menu This menu is used to create bookmarks to different documents for faster retrieval. Reports Menu Reports menu is used to create new reports and edit the existing reports. You can edit the layout, add pages to the report, and also delete reports. Tools Menu Tools menu is a very prominent menu, frequently used for creating charts and opening the QlikView management console. The important features in this menu are − Quick Chart Wizard creates simple charts without using the great number of different settings and options available. Time Chart Wizard creates time series charts. Statistics Chart Wizard is used to apply common statistical tests on data. Object Menu This menu is used to create new sheet objects and modify the existing ones. The sheet properties option opens the page to set the parameters defining the sheet. The important features in this menu are − Copy Sheet − creates a copy of the sheet along with the all the sheet objects. Copy Image to Clipboard − Copies a bitmap picture of the sheet area to Clipboard. Remove − completely removes the sheet along with the sheet objects. The Window and Help menus are used to organize the different windows of QlikView application and provide help documentation. Print Page Previous Next Advertisements ”;

QlikView – Installation ”; Previous Next Download QlikView The Free Personal Edition of QlikView can be downloaded from QlikView Personal Edition. You need to register with your details to be able to download. After downloading, the installation is a very straightforward process in which you need to accept the license agreement and provide the target folder for installation. The screen shots given below describe the entire setup process. Start the installation wizard Double clicking the QlikViewDesktop_x64Setup.exe will present a screen to select the language of your choice. On selecting English, the following screen is displayed. Then click Next. Accept the License Agreement Read the license agreement and if you agree, choose the “I accept the terms in the license agreement” option. Then click “Next”. Provide your Details Provide your name and organization details. Then Click “Next”. Choose the Destination Folder You may accept the default destination for installation or alter it. Then click “Next”. Choose the Setup Type Choose the setup type as “Complete”. Then click “Next”. Begin Installation In this screen, you finally decide to really start the installation. You can still go back and change some options if needed. Assuming you are fine with everything so far, click “Install”. Installation Complete The installation completion screen appears after successful installation. Click “Finish”. Verify the Installation You can verify the installation by going to the Windows Start menu and clicking on the QlikView icon. The screen appears as shown below. You are now ready to learn QlikView. Print Page Previous Next Advertisements ”;

QlikView – Rank Function ”; Previous Next The Rank() function in QlikView is used to display the rank of the values in a field as well as return rows with specific rank value. So it is used in two scenarios. First scenario is in QlikView charts to display the ranks of the values in the field and second is in Aggregate function to display only the rows, which have a specific rank value. Input Data The data used in the examples describing Rank function is given below. You can save this as a .csv file in a path in your system where it is accessible by QlikView. Product_Id,Product_Line,Product_category,Quantity,Value 1,Sporting Goods,Outdoor Recreation,12,5642 2,Food, Beverages & Tobacco,38,2514 3,Apparel & Accessories,Clothing,54,2365 4,Apparel & Accessories,Costumes & Accessories,29,4487 5,Sporting Goods,Athletics,11,812 6,Health & Beauty,Personal Care,21,6912 7,Arts & Entertainment,Hobbies & Creative Arts,58,5201 8,Arts & Entertainment,Paintings,73,8451 9,Arts & Entertainment,Musical Instruments,41,1245 10,Hardware,Tool Accessories,2,456 11,Home & Garden,Bathroom Accessories,36,241 12,Food,Drinks,54,1247 13,Home & Garden,Lawn & Garden,29,5462 14,Office Supplies,Presentation Supplies,22,577 15,Hardware,Blocks,53,548 16,Baby & Toddler,Diapering,19,1247 17,Baby & Toddler,Toys,9,257 18,Home & Garden,Pipes,81,1241 19,Office Supplies,Display Board,29,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 from the Data from Files tab and browse for the file containing the above data. Click OK and press Control+R to load the data into QlikView”s memory. Creating Chart with Rank() Function Next, we follow the steps given below to create a chart, which shows the rank of the filed Value described with respect to the dimension Product_Line. Select the Chart Type Click on the Chart wizard and choose the option straight table as the chart type. Click Next. Select the Chart Dimension From the First Dimension drop down list, choose Product_Line as dimension. Click Next. Define the Chart Expression In the custom expression field, mention the rank expression as shown below. Here we are considering the numeric field named Value, which represents the Sales value for each category under each Product Line. Click Next. Displaying the Chart On clicking Finish in the above step, the following chart appears which shows the rank of the sales value of each Product Line. Using Aggregate Function with Rank The aggregate functions like − max, min etc. can take rank as an argument to return rows satisfying certain rank values. We consider the following expression to be out in the script editor, which will give the rows containing highest sales under each Product line. # Load the records with highest sales value for each product line. LOAD Product_Line, max(Value,1) FROM [E:Qlikviewdataproduct_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq) group by Product_Line; 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 ”;