QlikView is a leading Business Discovery Platform. It is unique in many ways as compared to the traditional BI platforms. As a data analysis tool, it always maintains the relationship between the data and this relationship can be seen visually using colors. It also shows the data that are not related. It provides both direct and indirect searches by using individual searches in the list boxes.
QlikView's core and patented technology has the feature of in-memory data processing, which gives superfast result to the users. It calculates aggregations on the fly and compresses data to 10% of original size. Neither users nor developers of QlikView applications manage the relationship between data. It is managed automatically.
QlikView has patented technology, which enables it to have many features that are useful in creating advanced reports from multiple data sources quickly. Following is a list of features that makes QlikView very unique.
Data Association is maintained automatically − QlikView automatically recognizes the relationship between each piece of data that is present in a dataset. Users need not preconfigure the relationship between different data entities.
Data is held in memory for multiple users, for a super-fast user experience − The structure, data and calculations of a report are all held in the memory (RAM) of the server.
Aggregations are calculated on the fly as needed − As the data is held in memory, calculations are done on the fly. No need of storing pre-calculated aggregate values.
Data is compressed to 10% of its original size − QlikView heavily uses data dictionary. Only essential bits of data in memory is required for any analysis. Hence, it compresses the original data to a very small size.
Visual relationship using colors − The relationship between data is not shown by arrow or lines but by colors. Selecting a piece of data gives specific colors to the related data and another color to unrelated data.
Direct and Indirect searches − Instead of giving the direct value a user is looking for, they can input some related data and get the exact result because of the data association. Of course, they can also search for a value directly.
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.
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.
Read the license agreement and if you agree, choose the "I accept the terms in the license agreement" option. Then click "Next".
Provide your name and organization details. Then Click "Next".
You may accept the default destination for installation or alter it. Then click "Next".
Choose the setup type as "Complete". Then click "Next".
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".
The installation completion screen appears after successful installation. Click "Finish".
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 −
Layout Menu is used to add tabbed sheets, select different sheets and rearrange sheet objects. The important features in this menu are −
Settings menu is used to set the user preferences, document properties, and sheet properties. The important features in this menu are −
This menu is used to create bookmarks to different documents for faster retrieval.
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 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.
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.
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.
Keep the main window of QlikView open and browse for the excel file you want to use.
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.
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.
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.
On completing the above step, the Table Box Sheet Object appears which shows the data that is read from the Excel file.
QlikView can use the data in plane text file where the fields are separated by characters like comma, tab, semicolon etc. Here, we will take CSV as an example. A file in which each column of data is separated by a comma is known as a CSV file. It is a very widely used file format to store plane text-data organized as columns and rows.
QlikView loads csv files using the Data from files options available in the script editor under the File Menu. Alternatively, you can also open a new QlikView document and press control+E to get the script editor window as shown below. Choose the file Product_details.csv from the appropriate path.
On opening the selected CSV file, a window as shown below comes up. Under Labels choose Embedded Labels, as our file has a header row as its first row. Click Finish.
The loading of the file into QlikView is done through the load script, which can be seen in the screen shot below. Hence, when we use any delimited file, we can tweak the below script as per the file format.
Now the script 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 delimited file. We use a Table Box sheet object to display this data.
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.
On completing the above step, the Table Box Sheet Object appears which shows the data that is read from the Excel file.
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.
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.
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.
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.
On completing the above step, the Table Box Sheet Object appears which shows the data that is read from the Excel file.
QlikView can process files from the web, which are in the HTML format. It can extract data from HTML tables. The URL of the web file to be processed is given as an input and QlikView fetches both, the structure and content of the file. Then it analyzes the structure of the page extracting the relevant data from the HTML tables present in the page. We choose the Web files option from the Data from files section under the Data tab of script Editor.
On selecting the Web files option, we get a new window to give the URL as input. In this example, we are choosing the List of sovereign states and dependent territories in Asia as the input page from Wikipedia. Mention the URL and click Next.
On opening the selected Web file, the window shown below comes up. Here we can see the various tables present in the webpage labeled as @1, @1, @3 and so on. Choose the first table and click Next twice.
From the above table, we can choose only the columns we need by removing the unwanted columns using the cross sign.
The loading of the file into QlikView is done through the load script, which can be seen in the screen shot given below. Hence, when we use any delimited file, we can tweak the below given script as per the file format.
Now the script 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 web file. We use a Table Box sheet object to display this data.
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.
On completing the above step, the Table Box Sheet Object appears, which shows the data that is read from the Web file. Mark the Non-English characters !!
QlikView can connect to most of the popular databases like MySQL, SQL Server, Oracle, Postgress etc. It can fetch data and table structures into QlikView environment and store the results in its memory for further analysis. The steps to connect to any of these databases involves creating an ODBC connection using a DSN and then using this DSN to fetch the data.
For this tutorial, we will be connecting to MySQL database. This tutorial assumes you have a MySQL environment available. Create an ODBC DSN (Data Source Name) for MySQL, following these steps − to create DSN. Name the DSN as mysqluserdsn or you may prefer to use the existing one if you have already created a DSN for MySql.
For this chapter we will use the MySql inbuilt database named sakila. We create a new QlikView document and open the script editor (pressing Control+E). Under the tab Data, we locate the section named Database. Choose ODBC from the drop down list and click Connect. The following window opens. Choose the DSN named mysqluserdns and click Test Connection. The message Connection Test succeeded should appear.
On successful connection, the screen given below appears showing the connection to the DB in the main window of the script editor.
Click Select iin the above window to get the list of tables and columns. Here as we have created the DSN with sakila as the default database we get the list of tables and columns from this database. We can choose another database from the database drop down list as shown in the screenshot given below. We will continue using the sakila database for this chapter.
On Clicking OK in the above window, we get back to the main script editor showing the script for using the table named actor.
Now the data loaded into QlikView document needs to be stored permanently to be analyzed further. For this, we will edit the script to store the data in the form of a qvd file. Press Control+E to open the edit script window and write the following code.
In the code, we give appropriate names to the columns and mention the table name above the load statement. In addition, we give a path where the generated qvd file will be stored. Save this file as QV_mysql.qvw
The qvd file can be loaded into the main document and used to create graphs and tables for further analysis. Press Control+R to reload the QV_mysql.qvw file and click Next in the chart wizard. Choose the straight table to be created with actor_id, first_name, last_name as the dimensions and count of actor_id as the expression. A chart appears as given below.
Data can be entered into a QlikView document by directly typing or pasting it. This feature is a quick method to get the data from the clipboard into the QlikView. The script editor provides this feature under the Insert tab.
To open the Inline data load option, we open the script editor and go to Insert → Load Statement → Load Inline.
On opening the above screen, we get a spreadsheet-like document where we can type the values. We can also paste the values already available in the clipboard. Please note the column headers are created automatically. Click Finish.
The command, which loads the data, is created in the background which can be seen in the script editor.
On creating a Table Box Sheet Object, we see the data that is read from the Inline data load option.
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.
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.
On creating a Table Box Sheet Object, we see the data that is read from the resident data load option.
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.
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.
On creating a Table Box Sheet Object, we see the data that is read from the Inline data load option.
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
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.
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.
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.
The following chart showing the data as laid out in the previous step appears.
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
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:\Qlikview\data\products.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:\Qlikview\data\product_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:\Qlikview\data\products.qvd](qvd); //Store the values in qvd. store NewProducts into [E:\Qlikview\data\products.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.
One of the important features of QlikView, which makes it so distinguished is the ability to store very large amount of data in a very compressed size and store it along with the QlikView documents. Therefore, once the document is created we need not connect to the data source, as the data is already stored along with the layout of the document. This is achieved through QVD file, which is a flat file stored with the .qvd extension. A QVD file stores data for one QlikView document and it is created using the script editor available in the QlikView document.
The advantages of using QVD files in QlikView are as follows −
QVD files are created using the STORE statement during the loading of QlikView files. This statement creates a single qvd file, which gets stored in the specified location as a file; separate than the QVW file through which it is created.
Given below is an example of storing the qvd file after the data is loaded into the QlikView document by reading a source file.
A QVD file is loaded to a QlikView document in a similar way as other files like CSV, Excel and delimited files are used. We use the the Open option available under the File menu and browse for the QVD file we created before. On opening it gives us a window to see the data, select the column headers and do any data transformation required
On clicking Finish, the edit script window appears which shows the code used to load the QVD file. We can edit this code further. For example, to get only the few of the columns to be displayed or apply any inbuilt function etc. Click finish to load the file into the current QlikView document. Save the QlikView document as use_qvd.qvw.
Reload the document by using Control+R and choose the menu option Layout → New Sheet Objects → Table Box. A window appears showing all the columns from the table present in the QVD file. Select "Add All" to add all the columns to the display table. Use the "Promote/Demote" option to change the order of the columns. Click "Finish". The following screen appears showing the content of the QVD file.
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.
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.
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.
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.
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.
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 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.
Scripting is a very powerful feature in QlikView, which enables the control of the data load options and data transformations. It enables the use of many inbuilt functions available in QlikView and creates subroutines to be used across multiple scripts within a QlikViewdocument.
Scripting is done using the Script Editor. It is accessed from the File menu using Script Editor. We can also press Control +E to open the script editor window. It prepopulates some data formats that are set as default formats for the data to be processed. For example, the Thousand separator is a comma and date is in Month-day-year format. These can be changed to suit the incoming data as per the need.
Script editor has many features, which are accessed from the menu in the script editor window, which is a different menu from the main menu. Given below is a list of important features.
Reload − Reloads the script and fetches the new data.
Upper/Lower Case − Converts the case of words as QlikView is case sensitive.
Comment − Used to comment blocks of code.
Clear Entire Script − Clears the active script tab.
Open Script File − Opens the saved script files.
Table Viewer − Used to see the tabular view of the data being loaded.
Environment Variables − Inserts a standard list of Environment variables.
Script Files − Allows to browse for script files and insert them.
Connect/Disconnect Statement − Used to connect or disconnect from external databases.
Insert Tab − Inserts a Tab at the current cursor position.
Promote/Demote Tab − Allows to move the tabs from left to right and vice versa.
Merge with Previous − Used to merge the content of active tag with previous tab.
ODBC Administrator 64 bit/ODBC Administrator 32 bit − Allows to set the correct DSN information for data sources.
Editor Preferences − Allows you to configure the text font and size, help features, shortcuts, default-scripting engine etc.
Syntax Check − Used to validate the syntax of the script code.
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 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.
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.
QlikView IntervalMatch is a powerful function used to match distinct numeric values to numeric intervals. It is useful in analyzing how the events actually happened versus the planned events. The example of a scenario where it is used is in the assembly lines of the production houses where the belts are planned to run at certain times and for certain duration. However, the actual run can happen at different points in time because of breakdown etc.
Consider an assembly line where there are three belts named A, B and C. They are planned to start & stop at specific times of a day. In a given day, we study the actual start and end time and analyze what all happened in that day. For this, we consider two sets of observations as shown below.
# Data Set for AssembilyLine. StartTime,EndTime, BeltNo 00:05,4:20, A 1:50,2:45,B 3:15,10:30,C # Data set for the events happened. ActualTime,Product 1:10,Start Belt A 2:24,Stop Belt A 3:25,Restart Belt A 4:35,Stop Belt A 2:20,Start Belt B 3:11, Stop Belt B 3:15,Start Belt C 11:20, Stop Belt C
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.
Let us create a Table Box sheet object to show the data generated by the IntervalMatch function. 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.
On clicking OK in the above window, a table appears showing the field ActualTime matched to the intervals StartTime and EndTime.
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.
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
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.
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.
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.
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:\Qlikview\data\product_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.
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:\Qlikview\data\product_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.
The Match() function in QlikView is used to match the value of a string on expression with data value present in a column. It is similar to the in function that we see in SQL language. It is useful to fetch rows containing specific strings and it also has an extension in form of wildmatch() function.
Let us consider the following data as input file for the examples illustrated below.
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 13,Office Supplies,Presentation Supplies,Display 14,Hardware,Tool Accessories,Jigs 15,Baby & Toddler,Diapering,Baby Wipes
The following script shows the Load script, which reads the file named product_categories.csv. We search the field Product_Line for values matching with strings 'Food' and 'Sporting Goods'.
Let us create a Table Box sheet object to show the data generated by the match 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 then select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below.
The wildmatch() function is an extension of match() function in which we can use wildcards as part of the strings used to match the values with values in the fields being searched for. We search for the strings 'Off*','*ome*.
Let us create a Table Box sheet object to show the data generated by the wildmatch function. 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 then select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below.
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.
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
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.
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.
Click on the Chart wizard and choose the option straight table as the chart type. Click Next.
From the First Dimension drop down list, choose Product_Line as dimension. Click Next.
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.
On clicking Finish in the above step, the following chart appears which shows the rank of the sales value of each Product Line.
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:\Qlikview\data\product_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq) group by Product_Line;
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.
The peek() function in QlikView is used to fetch the value of a field from a previous record and use it in calculations.
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
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:\Qlikview\data\monthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
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.
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:\Qlikview\data\monthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
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.
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.
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
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:\Qlikview\data\monthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
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:\Qlikview\data\monthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
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.
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.
We can quickly find out the basic information of a QlikView document. Click on Help → document Support Info. Given below is a sample output.
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.
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.
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.
A list box represents the list of all the values of a specific field. Selecting a value in list box highlights the related values in other sheet objects. This helps in faster visual analysis. It is also very useful to follow a drill down path among various sheet objects. It also has a search feature, which allows to search for specific values in the list box which is very helpful for a very long list of values.
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
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 Product_Line, Product_category, Value FROM [C:\Qlikview\data\product_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Creation of List Box involves navigating through menu Layout → New Sheet Object → List Box. The following screen shows these steps.
Next, we choose Product category as the field on which we build the list box.
Finishing the above steps brings the following screen, which shows the values of Product category as a list box.
When the List Box contains very large number of values, it is difficult to scroll down and look for it. So the search box at the top of the list box can be used to type the search string. The relevant values appear as soon as the first letter is typed.
Other Sheet Objects automatically get associated with the List Box and the association is easily observed by selecting values form the list box.
A Multi Box represents the list of all the values from multiple fields as drop down values. Similar to list box, the selection of a value in Multi Box highlights the related values in other sheet objects. This helps in faster visual analysis. It is also very useful to follow a drill down path among various sheet objects.
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
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 the QlikView's memory.
LOAD Product_Line, Product_category, Value FROM [C:\Qlikview\data\product_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Creation of Multi Box involves navigating through menu Layout → New Sheet Object → Multi Box. The following screen shows these steps.
Next we choose the fields of the Products sales tables to build the Multi Box.
Finishing the above steps brings the following screen, which shows the values of Product category as a Multi box.
Other Sheet Objects automatically get associated with the Multi Box and the association is easily observed by selecting values from the Multi Box.
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.
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
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:\Qlikview\data\product_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
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.
For the above data, let us create a Text Object. Go to the menu Layout → New Sheet Object → Text Object as shown below.
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.
The background color of the Text Object can be set using the background option in the General tab.
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.
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.
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
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:\Qlikview\data\product_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
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.
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 Product Line as the First Dimension.
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.
The Chart format defines the style and orientation of the chart. We choose the first option in each category. Click Next.
The Bar chart appears as shown below. It shows the height of the field value for different product lines.
A pie-chart is a representation of values as slices of a circle with different colors. The slices are labeled and the numbers corresponding to each slice is also represented in the chart. QlikView creates pie-chart using the chart wizard or chart Sheet Object.
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
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:\Qlikview\data\product_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
To start creating a Pie chart, we will use the quick chart wizard. On clicking it, the following screen appears which prompts for selecting the chart type. Choose Pie Chart and click Next.
Choose Product Line as the First Dimension.
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.
The Chart format defines the style and orientation of the chart. We choose the third option. Click Next.
The Bar chart appears as shown below. It shows the height of the field value for different product lines.
A Dashboard is a powerful feature to display values from many fields simultaneously. QlikeView's feature of data association in memory can display the dynamic values in all the sheet objects.
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
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:\Qlikview\data\product_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
We choose the fields from the above input data as matrices to be displayed in the dashboard. For this, we follow the steps in the menu Layout → Select Fields.
In the next screen, choose the available fields to be displayed in the dashboard. Click "OK".
The following screen appears displaying all the fields
NNow we add a chart to the dashboard by right-clicking anywhere in the sheet and choosing New Sheet Object → Chart.
Let us choose the chart type as a bar chart to display the sales values for various product Lines.
Let us select the Product Line as the Chart Dimension.
The expression to display the sales value for the Product Line dimension is written in the expression editor.
Given below is the dashboard displayed after finishing the above steps.
The values in the above Dashboard can be selected for filtering specific products and the chart changes accordingly. In addition, the associated values are highlighted.
Data Transformation is the process of modifying the existing data to a new data format. It can also involve filtering out or adding some specific values to the existing data set. QlikView can carry out data transformation after reading it to its memory and using many in-built functions.
Let us consider the following input data, which represents the sales figures of each month. This is stored as a csv file with name quarterly_sales.csv
Month,SalesVolume March,2145 April,2458 May,1245 Sales Values in Q2 June,5124 July,7421 August,2584 Sales Values in Q3 September,5314 October,7846 November,6532 December,4625 January,8547 February,3265
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 form the "Data from Files" tab and browse for the file quarterlt_sales.csv. Click next.
The next screen prompts us to choose some data transformation. Click on the button Enable Transformation Step.
In this step, we will select the transformation to eliminate the rows, which describe the quarter. We select Garbage → delete marked and select the two rows, which are not required. Click Next.
After selecting the type of Transformation and the rows to be removed, the next screen prompts us for any further transformation like selecting a where clause or adding any Prefixes. We will ignore this step and click Finish.
The Load script for the above data after all the transformation steps are complete is given below.
The transformed data can be displayed by using a Table Box sheet object. The steps to create it are given below.
Next, we choose the fields for the Table Box.
The Table Box now displays the data in the sheet.
The Fill function in QlikView is used to fill values from existing fields into a new field.
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,
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.
After clicking Next in the above step, we choose the Enable Transformation Step button to carry out the required data transformation.
As we are going to use the Fill function, let us choose the Fill tab, which displays th empty values under the Actual Field.
On clicking the Fill button, the option to choose target column and the cell condition appears. We choose column three, as we want to fill the empty values of this column with values from same row in column two. Also, choose the Cell Value as empty so that only the empty cells will be overwritten with new values.
On completing the above steps, we get the transformed data as shown below.
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.
The transformed data can be seen by creating a Table Box using the option in the menu Layout → New Sheet Object.
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.
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
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.
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.
On completing the above steps, we get the transformed data as shown below.
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.
The transformed data can be seen by creating a Table Box using the option in the menu Layout → New Sheet Object.
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.
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,
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.
As we are going to use the Rotate function, let us choose the Rotate tab which displays the values of all the fields.
We click the Transpose button to transpose the above data. The transposed data appears as shown below.
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.
The transformed data can be seen by creating a Table Box using the option in the menu Layout → New Sheet Object.
Dimensions and Measures are fundamental entities, which are always used in data analysis. For example, consider the result of the analysis, “what is the percentage change in volume of sales for each quarter?” In this case, each quarter represents the Dimensions, which is the name of the quarter. The percentage change in volume represents the Measures, which is a calculation with respect to each value in the dimension. Below are some widely accepted definition of these two terms.
Let us consider the following input data, which represents the sales volume and Revenue of different product lines and product categories in different regions. Save the data into a .csv file.
ProductID,ProductCategory,Region,SalesVolume, Revenue 1,Outdoor Recreation,Europe,457,25841 2,Clothing,Europe,125,54281 3,Costumes & Accessories,South Asia,781,54872 4,Athletics,South Asia,839,87361 5,Personal Care,Australia,473,15425 6,Arts & Entertainment,North AMerica,625,84151 7,Hardware,South America,772,45812
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 the QlikView's memory
We can see the structure of the table by following the menu File → Table Viewer or pressing Control+T. The following screen comes up in which we have marked the dimensions inside a green box and the measures inside a red box.
Let us create a straight table chart showing the calculation using above dimensions and measures. Click on the Quick Chart Wizard as shown below.
Next, click on the Straight Table option. Click Next.
In this screen, we choose Region as the dimension as we want to select the total revenue for each region.
The Next screen prompts for applying the calculation on a measure field. We choose to apply Sum on the field Revenue.
On completing the above steps, we get the final chart which shows the total revenue(Measure) for each region(Dimension).
A start schema model is a type of data model in which multiple dimensions are linked to a single fact table. Of course, in bigger models there can be multiple facts tables linked to multiple dimensions and other fact tables. The usefulness of this model lies in performing fast queries with minimal joins among various tables. The fact table contains data, which are measures and have numeric values. Calculations are applied on the fields in the fact table. The unique keys of the dimension tables are used in linking it to the fat table, which also has a key usually with the same field name. Therefore, the Fact table contains the keys from the entire dimension table and forms a concatenated primary key used in various queries.
Given below is a list of tables, which contain the data for different products from various suppliers and regions. Also the supply happens at different time intervals, which are captured in the Time dimension table.
It contains the Product Category and Product Names. The Product ID field is the unique Key.
ProductID,ProductCategory,ProductName 1,Outdoor Recreation,Winter Sports & Activities 2,Clothing,Uniforms 3,Lawn & Garden Power, Equipment 4,Athletics,Rugby 5,Personal Care,Shaver 6,Arts & Entertainment,Crafting Materials 7,Hardware,Power Tool Batteries
It contains the Region Names where the suppliers are based. The RegionID field is the unique Key.
RegionID,Continent,Country 3,North America, USA 7,South America, Brazil 12,Asia,China 2,Asia,Japan 5,Europe,Belgium
It contains the Supplier Names, which supply the above products. The SupplierID field is the unique Key.
SupplierID,SupplierName 3S12,Supre Suppliers 4A15,ABC Suppliers 4S66,Max Sports 5F244,Nice Foods 8A45,Artistic angle
It contains the Time periods when the supply of the above products occur. The TimeID field is the unique Key.
TimeID,Year,Month 1,2012,Feb 2,2012,May 3,2012,Sep 4,2013,Aug 5,2014,Jan 6,2014,Nov
It contains the values for the quantities supplied and percentage of defects in them. It joins to each of the above dimensions through keys with same name.
ProductID,RegionID,TimeID,SupplierID,Quantity, DefectPercentage 1,3,3,5F244,8452,12 2,3,1,4S66,5124,8.25 3,7,1,8A45,5841,7.66 4,12,2,4A15,5123,1.25 5,5,3,4S66,7452,8.11 6,2,5,4A15,5142,3.66 7,2,1,4S66,452,2.06
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. Click OK and press Control+R to load the data into QlikView's memory. Below is the script which appears after each of the above file is read.
LOAD ProductID, ProductCategory, ProductName FROM [C:\Qlikview\images\StarSchema\Product_dimension.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); LOAD TimeID, Year, Month FROM [C:\Qlikview\images\StarSchema\Time.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); LOAD SupplierID, SupplierName FROM [C:\Qlikview\images\StarSchema\Suppliers.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); LOAD RegionID, Continent, Country FROM [C:\Qlikview\images\StarSchema\Regions.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); LOAD ProductID, RegionID, TimeID, SupplierID, Quantity, DefectPercentage FROM [C:\Qlikview\images\StarSchema\Supplier_quantity.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
After reading the above data into QlikView memory, we can look at the data model, which shows all the tables, fields, and relationship in form of a star schema.
A Synthetic Key is QlikView's solution to create an artificial key when there is ambiguity about which key to use between two tables. This situation arises when two tables have two or more fields in common. QlikView's feature of creating association in memory automatically detects this scenario and creates an additional table, which will hold the value of the new key created.
Let us consider the following two CSV data files, which are used as input for further illustrations.
Sales: ProductID,ProductCategory,Country,SaleAmount 1,Outdoor Recreation,Italy,4579 2,Clothing,USA,4125 3,Costumes & Accessories,South Korea,6521 Product: ProductID, Country 3,Brazil 3,China 2,Korea 1,USA
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.
Next, we look at the data model by using the menu command for table viewer, Control+T. The following screen comes up, which shows the creation of a third table that supplies the value of the synthetic key as both the tables have ProductID and Country as matching keys.
Synthetic keys indicate the flaw in the data model that is being used. They do not cause any issue in the correctness of the data or performance of the report. Things will work fine if a big data model has one or two instances of synthetic keys. However, if we have too many of them, then that is an implication to redesign the data model.
Many times, we need some data to be generated programmatically by the software being used, which is not coming from a source. For example, 100 random numbers or just the dates of 23rd week of a year. A data analyst may need such data to be created to perform some analysis on the data that does not contain these values as it arrived. QlikView provides a function called Autogenerate, which can be used for such requirement.
Consider a scenario where we need to find only the dates, which are a Thursday or a Sunday. We need to find it for the range starting today until the end of the year. We create the following script, which will achieve this.
We declare two variables to capture the first day of the current month and end of the year. Next we apply various functions and a filter condition to generate the required values. The recno() function creates one record for each of these dates. We add Autogenerate function giving the variables as the range.
On loading the above script to QlikView's memory and creating a Table Box using the menu Layout → New Sheet Objects → Table Box, we get the data created as shown below.
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.
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
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.
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.
The transformed data appears in which all the Region fields are clubbed to one column but with values repeating for each quarter.
The Load script for the crosstable transformations shows the commands given below.
On creating a Table Box sheet object using the menu Layout → New Sheet Objects → Table Box, we get the following output.
Straight Tables are most widely used sheet object to display data in QlikView. They are very simple yet powerful with features like column rearrangement, sorting and coloring the background etc.
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
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. The following screen appears.
Click "OK" and press "Control+R" to load the data into the QlikView's memory
Next, we create a new sheet Object of type Table Box. We follow the menu as shown below.
QlikView prompts for the columns to be chosen which will be displayed in the final Table Box. We choose all the columns and use the Promote or Demote option to set the order of the columns.
Next, we choose the style tab to give specific background colors to the display data. The current style option lists many pre-built styles. We choose Pyjama Red with Stripes every two rows.
We can reorder the positions of the columns by pressing and holding the mouse button at the column headers and then dragging it to the desired position.
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.
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
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.
Next, we use the chart wizard to select the Pivot Table option. Click Next.
In the next screen, we choose Product_Line as the first dimension for the chart.
The next screen prompts us for selecting the chart expression where we choose the sum of value.
On clicking next, we get the screen to choose chart format in which we select Pyjama Green as the style and the default mode.
Completing the above steps gives us the final chart as below.
QlikView's Set Analysis feature is used to segregate the data in different sheet objects into many sets and keeps the values unchanged in some of them. In simpler terms, it creates an option to not associate some sheet objects with others while the default behavior is all sheet objects get associated with each other. This helps in filtering the data in one sheet object and seeing the corresponding result in others, while the sheet object chosen as a different set displays values as per its own filters.
Let us consider the following input data, which represents the sales figure of different product lines and product categories.
Product_Line,Product_category,Month,Value Arts & Entertainment,Hobbies & Creative Arts,Jan,5201 Arts & Entertainment,Paintings,Feb,8451 Arts & Entertainment,Musical Instruments,Jan,1245 Baby & Toddler,Diapering,Mar,1247 Baby & Toddler,Toys,Dec,257 Apparel & Accessories,Clothing,Feb,574 Apparel & Accessories,Costumes & Accessories,Apr,1204 Arts & Entertainment,Musical Instruments,Apr,3625 Baby & Toddler,Diapering,Apr,1281 Apparel & Accessories,Clothing,Jul,2594 Arts & Entertainment,Paintings,Sep,6531 Baby & Toddler,Toys,May,7421 Apparel & Accessories,Clothing,Aug,2541 Arts & Entertainment,Paintings,Oct,2658 Arts & Entertainment,Musical Instruments,Mar,1185 Baby & Toddler,Diapering,Jun,1209
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. A screen appears as shown below.
Choose all the fields available to create a table box using the menu option Layout → New Sheet Objects → Table Box and a list box containing the month’s field using the menu option Layout → New Sheet Objects → List Box. Also, create a straight table chart showing the total sales under each product category.
Now we can observe the association between these three sheet objects by selecting some values in one of them. Let us select the month Apr and Jan from the Month list Box. We can see the change in values in the Table Box and chart showing the related values.
Next, we clone the sales sum chart to produce a new set of data not associated with other sheet objects. Right click on the chart Sales Sum and click on the option Clone as shown below. Another copy of the same chart appears in the QlikView document.
Next, we choose the second copy of the chart Sales Sum and right click it to get the chart properties. We create an expression called Sales values writing the formula under the Definition tab as shown below.
On completing the above given steps, we find that when we select the month June we get the associated values in the Table Box and Sales Sum chart. However, the April sales does not change as it is based on the data from the set expression.
Joins in QlikView are used to combine data from two data sets into one. Joins in QlikView mean the same as in joins in SQL. Only the column and row values that match the join conditions are shown in the output. In case you are completely new to joins, you may like to first learn about them here.
Let us consider the following two CSV data files, which are used as input for further illustrations.
Product List: ProductID,ProductCategory 1,Outdoor Recreation 2,Clothing 3,Costumes & Accessories 4,Athletics 5,Personal Care 6,Hobbies & Creative Arts ProductSales: ProductID,ProductCategory,SaleAmount 4,Athletics,1212 5,Personal Care,5211 6,Hobbies & Creative Arts,1021 7,Display Board,2177 8,Game,1145 9,soap,1012 10,Beverages & Tobacco,2514
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 create an inner join between the tables.
Inner join fetches only those rows, which are present in both the tables. In this case, the rows available in both Product List and Product Sales table are fetched. We create a Table Box using the menu Layout → New Sheet Objects → Table Box where we choose all the three fields - ProductID, ProductCategory and SaleAmount to be displayed.
Left join involves fetching all the rows from the table in the left and the matching rows from the table in the right.
Sales: LOAD ProductID, ProductCategory, SaleAmount FROM [C:\Qlikview\data\product_lists.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); LEFT JOIN(Sales) LOAD ProductID, ProductCategory FROM [C:\Qlikview\data\Productsales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
We create a Table Box using the menu Layout → New Sheet Objects → Table Box, where we choose all the three fields − ProductID, ProductCategory and SaleAmount to be displayed.
Right join involves fetching all the rows from the table in the right and the matching rows from the table in the left.
Sales: LOAD ProductID, ProductCategory, SaleAmount FROM [C:\Qlikview\data\product_lists.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); RIGHT JOIN(Sales) LOAD ProductID, ProductCategory FROM [C:\Qlikview\data\Productsales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
We create a Table Box using the menu Layout → New Sheet Objects → Table Box, where we choose all the three fields - ProductID, ProductCategory and SaleAmount to be displayed.
Outer join involves fetching all the rows from the table in the right as well as from the table in the left.
Sales: LOAD ProductID, ProductCategory, SaleAmount FROM [C:\Qlikview\data\product_lists.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); OUTER JOIN(Sales) LOAD ProductID, ProductCategory FROM [C:\Qlikview\data\Productsales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
We create a Table Box using the menu Layout → New Sheet Objects → Table Box where we choose all the three fields - ProductID, ProductCategory and SaleAmount to be displayed.
The keep command in QlikView is used to combine data from two data sets keeping both the data sets available in memory. It is very similar to joins we covered in the previous chapter except for two major differences. First difference is − in case of keep; both the datasets are available in QlikView's memory while in join the load statements produce only one data set from which you have to choose the columns. The second difference being − there is no concept of outer keep where as we have outer join available in case of joins.
Let us consider the following two CSV data files, which are used as input for further illustrations.
Product List: ProductID,ProductCategory 1,Outdoor Recreation 2,Clothing 3,Costumes & Accessories 4,Athletics 5,Personal Care 6,Hobbies & Creative Arts Product Sales: ProductID,ProductCategory,SaleAmount 4,Athletics,1212 5,Personal Care,5211 6,Hobbies & Creative Arts,1021 7,Display Board,2177 8,Game,1145 9,soap,1012 10,Beverages & Tobacco,2514
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 create an inner keep between the tables.
Inner keep fetches only those rows, which are present in both the tables. In this case, the rows available in both Product List and Product Sales table are fetched. We create a Table Boxes using the menu Layout → New Sheet Objects → Table Box.
First, we choose only the productSales table, which gives us the fields - ProductID, ProductCategory and SaleAmount to be displayed.
Next, we choose the ProductList data set, which gives us the fields ProductID and ProductCategory.
Finally, we choose the All Tables option and get all the available fields from all the tables.
The following report shows all the Tables Boxes from the above given steps.
Left keep is similar to left join, which keeps all the rows from the table in the left along with both the data set being available in QlikView's memory.
The following script is used to create the resulting data sets with left keep command.
productsales: LOAD ProductID, ProductCategory, SaleAmount FROM [C:\Qlikview\data\product_lists.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); left keep(productsales) productlists: LOAD ProductID, ProductCategory FROM [C:\Qlikview\data\Productsales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
When we change the script as above and refresh the data in the report using Control+R, we get the following data in the sheet objects.
Right keep is similar to left join, which keeps all the rows from the table in the right along with both the data set being available in QlikView's memory.
The following script is used to create the resulting data sets with left keep command.
productsales: LOAD ProductID, ProductCategory, SaleAmount FROM [C:\Qlikview\data\product_lists.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); right keep(productsales) productlists: LOAD ProductID, ProductCategory FROM [C:\Qlikview\data\Productsales.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
When we change the script as above and refresh the data in the report using Control+R, we get the following data in the sheet objects.
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.
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
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.
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.
In QlikView, many times we need to create a calendar reference object, which can be linked to any data set present in QlikView's memory. For example, you have a table that captures the sales amount and sales date but does not store the weekday or quarter, which corresponds to that date. In such a scenario, we create a Master Calendar which will supply the additional date fields like Quarter, Day etc. as required by any data set.
Let us consider the following CSV data files, which are used as input for further illustrations.
SalesDate,SalesVolume 3/28/2012,3152 3/30/2012,2458 3/31/2012,4105 4/8/2012,6245 4/10/2012,5816 4/11/2012,3522
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.
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.
Next, we create the Master Calendar by writing the following script in the script editor. Here we use the table DailySales as a resident table from which we capture the Maximum and Minimum dates. We load each of the dates within this range using the second load statement above the resident load. Finally, we have a third load statement, which extracts the year, quarter, month etc. from the SalesDate values.
After creation of the complete load script along with the master calendar, we create a table box to view the data using the menu Layout → New Sheet Objects → Table Box
The final output shows the table showing the Quarter and Month values, which are created using the Sales data and Master Calendar.
Mapping table is a table, which is created to map the column values between two tables. It is also called a Lookup table, which is only used to look for a related value from some other table.
Let us consider the following input data file, which represents the sales values in different regions.
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 8,Home & Garden,South America,241 9,Food,South Asia,1247 10,Home & Garden,South Asia,5462 11,Office Supplies,Australia,577
The following data represents the countries and their regions.
Region,Country Europe,Germany Europe,Italy South Asia,Singapore South Asia,Korea North AMerica,USA South America,Brazil South America,Peru South Asia,China South Asia,Sri Lanka
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. Click OK and ess Control+R to load the data into the QlikView's memory.
Let us create two table boxes for each of the above table as shown below. Here we cannot get the value of country in the Sales region report.
The following script produces the mapping table, which maps the region value from the sales table with the country value from the MapCountryRegion table.
On completing the above steps and creating a Table box to view the data, we get the country columns along with other columns from Sales table.
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.
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
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 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.
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.
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.
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.