Category: qlikview

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

QlikView – Incremental Load ”; Previous Next As the volume of data in the data source of a QlikView document increases, the time taken to load the file also increases which slows down the process of analysis. One approach to minimize this time taken to load data is to load only the records that are new in the source or the updated ones. This concept of loading only the new or changed records from the source into the QlikView document is called Incremental Load. To identify the new records from source, we use either a sequential unique key or a date time stamp for each row. These values of unique key or data time field has to flow from the source file to QlikView document. Let us consider the following source file containing product details in a retail store. Save this as a .csv file in the local system where it is accessible by QlikView. Over a period of time some more products are added and the description of some product changes. Product_Id,Product_Line,Product_category,Product_Subcategory 1,Sporting Goods,Outdoor Recreation,Winter Sports & Activities 2,”Food, Beverages & Tobacco”,Food Items,Fruits & Vegetables 3,Apparel & Accessories,Clothing,Uniforms 4,Sporting Goods,Athletics,Rugby 5,Health & Beauty,Personal Care 6,Arts & Entertainment,Hobbies & Creative Arts,Musical Instruments 7,Arts & Entertainment,Hobbies & Creative Arts,Orchestra Accessories 8,Arts & Entertainment,Hobbies & Creative Arts,Crafting Materials 9,Hardware,Tool Accessories,Power Tool Batteries 10,Home & Garden,Bathroom Accessories,Bath Caddies 11,”Food, Beverages & Tobacco”,Food Items,Frozen Vegetables 12,Home & Garden,Lawn & Garden,Power Equipment Loading the Data into QlikView We will load the above CSV file using the script editor (Control+E) by choosing the Table Files option as shown below. Here we also save the data into a QVD file in the local system. Save the QlikView document as a .qvw file. Verifying the Data Loaded. We can check the data loaded to QlikView document by creating a sheet object called Table Box. This is available in the Layout menu and New Sheet Objects sub-menu. Creating the Table Layout On selecting the Table Box sheet object, we get to the next screen, which is used to select the columns and their positions in the table to be created. We choose the following columns and their positions and click Finish. Viewing the Existing Data The following chart showing the data as laid out in the previous step appears. Updating the Source Data Let us add the following three more records to the source data. Here, the Product IDs are the unique numbers, which represent new records. 13,Office Supplies,Presentation Supplies,Display 14,Hardware,Tool Accessories,Jigs 15,Baby & Toddler,Diapering,Baby Wipes Incremental load script Now, we write the script to pull only the new records form the source. // Load the data from the stored qvd. Stored_Products: LOAD Product_Id, Product_Line, Product_category, Product_Subcategory FROM [E:Qlikviewdataproducts.qvd] (qvd); //Select the maximum value of Product ID. Max_Product_ID: Load max(Product_Id) as MaxId resident Stored_Products; //Store the Maximum value of product Id in a variable. Let MaxId = peek(”MaxId”,-1); drop table Stored_Products; //Pull the rows that are new. NewProducts: LOAD Product_Id,Product_Line, Product_category,Product_Subcategory from [E:Qlikviewdataproduct_categories.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq) where Product_Id > $(MaxId); //Concatenate the new values with existing qvd. Concatenate LOAD Product_Id,Product_Line, Product_category, Product_Subcategory FROM [E:Qlikviewdataproducts.qvd](qvd); //Store the values in qvd. store NewProducts into [E:Qlikviewdataproducts.qvd](qvd); The above script fetches only the new records, which are loaded and stored into the qvd file. As we see the records with the new Product IDs 13, 14 and 15. Print Page Previous Next Advertisements ”;

QlikView – Sheet and Objects ”; Previous Next Every QlikView document is made of at least one worksheet called Main. We can add more sheets, which are like many pages of the same QlikView document. Sheets help us display multiple data formats like – multiple charts or multiple tables. Each sheet can contain various sheet objects. In addition, sheets can be rearranged using Promote Sheet/Demote Sheet option and can be removed from the QlikView document using Remove Sheet option. Sheet Properties Sheets have various properties, which can be set to customize the sheets. For example, we can set the name of the sheets and its colors. Right click anywhere in the sheet and choose the Properties option. Then choose the following properties. Sheet Settings → Color. − This will set the background colour of the Sheet. Tab Settings → Custom Colors. − This will set the color for the Tab where the Sheet name appears. Title. − This will set the name of the Sheet. Creating Sheet Objects Sheet Objects are the QlikView data elements that are embedded in the sheet. They display the data that is loaded into the QlikView”s memory. Each sheet object is tied to a data source and one or more of its columns. Sheet Objects are created from the layout menu as shown below. Using Sheet Objects Sheet Objects display the data from a data source and all the objects in a sheet are associated with each other. Let”s create a List Box and a Multi Box and see this association on action. Creating List Box The List box displays data from a column of a table available in QlikView memory. Choose the option List Box from the Add Sheet Objects option and set the properties as given below. Creating Multi Box A Multi Box represents data from multiple columns from a table. Choose the option Multi Box from the Add Sheet Objects option and set the properties as shown below. On completing the above given steps, the following window appears which shows both the sheet objects. Association between Sheet Objects We can see how the sheet objects are linked to each other by choosing the one option from the Multi Box, which highlights the associated row in the List Box. Let us choose “Diapering” under the Product Category drop down list in Multi Box. The window shown below appears. Print Page Previous Next Advertisements ”;

QlikView – Resident Load ”; Previous Next QlikView can load data from tables already existing in its RAM, which is already processed by a script. This requirement arises when you want to create a table deriving data from an already existing table in the same script. Please note that both the new table and the existing table should be in the same script. Creating the Load Script Open the script editor (or use Control+E) and mention the following script. Here we create an inline table named Regions with sales data for different regions. Then we create another table named Total to calculate the total sales by Region Names. Finally we drop the table Regions, as in this .qvw file we only need the table named Total for data analysis. Table Box Data On creating a Table Box Sheet Object, we see the data that is read from the resident data load option. 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 – 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 ”;

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