Category: qlikview

QlikView – Inline Data ”; Previous Next 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. Script Editor To open the Inline data load option, we open the script editor and go to Insert → Load Statement → Load Inline. Inserting Data 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. Load Script The command, which loads the data, is created in the background which can be seen in the script editor. Table Box Data On creating a Table Box Sheet Object, we see the data that is read from the Inline data load option. Print Page Previous Next Advertisements ”;

QlikView – Useful Resources ”; Previous Next The following resources contain additional information on QlikView. Please use them to get more in-depth knowledge on this topic. QlikView Scripting Master Projects : Beginner to Advanced 70 Lectures 5 hours Arthur Fong More Detail QlikView Course: Beginner To Advanced 75 Lectures 3.5 hours Prathmesh Bendkhale More Detail Data Analytics MasterClass(PostgreSQL, BI, Python) 134 Lectures 8.5 hours Arthur Fong More Detail Print Page Previous Next Advertisements ”;

QlikView – Mapping Tables ”; Previous Next 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. Input Data 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 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from the Data from Files tab and browse for the file containing the above data. Click OK and ess Control+R to load the data into the QlikView”s memory. Create Table Box 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. Create the Mapping Table The following script produces the mapping table, which maps the region value from the sales table with the country value from the MapCountryRegion table. Table Chart 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. Print Page Previous Next Advertisements ”;

QlikView – Interview Questions ”; Previous Next Dear readers, these QlikView Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of R programming. As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer − What are the unique features of QlikView? (a)Data Association is maintained automatically. (b) The structure, data and calculations of a report are all held in the memory (RAM) of the server. (c) Data is compressed to 10% of its original size. (d) Visual relationship using colors. What is Incremental Load? The concept of loading only the new or changed records from the source into the QlikView document is called Incremental Load. How you connect QlikView to Database? QlikView can connect to data base using ODBC connection created for the database. What is Dashboard? A dashboard is a QlikView document which shows many matrices together and the values in the sheet objects can change dynamically upon selection of certain value in one of the Sheet Objects. Why do we need a Master Calendar? The Master calendar is required when we want to create some additional date values which are not already captured in the data that is being analyzed. For example finding the quarter to which a given date falls etc. What is Aggr Function? AGGR statement function produces a virtual table, with one expression and grouped by one or more dimensions. The result of this virtual table can then be used by a further outer aggregation function(s). What is a star schema? A Star schema is a data model in which one fact table is connected to multiple dimension tables though foreign keys What is the difference between Join and Keep? 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. Also there is no concept of outer keep where as we have outer join available in case of joins. What is Synthetic key, is it good or bad to have? QlikView creates a synthetic key when two or more column between tables are same. It does not impact the data or performance but it indicates a flaw in the data model design. Difference between Join and Concatenate? Join gives the resulting records from two tables as records containing columns form both tables. But Concatenate only appends the rows from one table with another. What are Circular loops in QlikView? A circular loop is created when the relationship between two tables can be established both directly and through another third table. What does MonthStart function do? Returns a value corresponding to a timestamp with the first millisecond of the first date of the month containing date. What does Autogenerate Function do? This function auto generates values between a given range of numbers. What is a pivot table in QlikView? Pivot Tables are used to present sum of values across many dimensions available in the data. For example showing the total sales figure for both the months and quarters in a years. What are the 6 chart types available in QlikView? Bar Chart, Pie Chart, Line Chart, Gauge Chart, Pivot table, Straight table. Can QlikView extract data from website? How? Yes. In QlikView script editor we have the option to extract data form a web file by giving the URL as the input. What is the use of Promote/Demote options in a Table Box property? It allows you to rearrange the columns in the Table box which is displayed in the Sheet. What are the three options available under the Rotate table functionality for data transformation? Rotate Left, Rotate Right and Transpose. What are the parameters required by a Crosstable Wizard to create a cross table? There are three fields required to create a cross table. Qualifier field, Attribute Field and Data Field. What does Partial Reload do? It Executes the current load script, including all script commands, such as Drop Table and reloads data to the active QlikView document. However, only those tables whose load and select statements are preceded by the Replace or Add prefix are reloaded. How can we see the table structures of data loaded to QlikView’s memory? By using the Table Viewer Option under file menu we can see the Tables and their relationships. What is the use of – Export Sheet layout? When we want to preserve the layout of a sheet to be used again, we export the sheet layout which creates a XML file without any data. What is Webview Mode? The WebView mode uses the internal web browser in QlikView to display the document layout as an AJAX page. What is a selection indicator in QlikView Document? A selection indicator is used to indicate the type of association between the data present in different sheet objects. A green dot indicates selected values, blue dot indicates locked values and red dot indicates de-selected values in AND mode. What does *bi* in text search mean? It searches for any string that contains bi. What is Fuzzy search in QlikView? Fuzzy search finds all the values according to their degree of resemblance to the search string. Which means, even if the spelling does not match character by character, those results will also be shown. What is a Bookmark in QlikView? A bookmark in QlikView captures the selections in all states defined in a QlikView document. It can be saved and accessed later. What is a user bookmark and a shared server bookmark? The User bookmark is saved in the user computer while the shared server bookmark is saved in the server and accessible to all the allowed users. What are the different ways in which the QlikView Alerts can be triggered? The alerts

QlikView – Star Schema ”; Previous Next 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. Input Data 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. Product Dimension 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 Region Dimension 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 Supplier Dimension 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 Time Dimension 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 Supplier Quantity Fact 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 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from the Data from Files tab and browse for the file containing the above data. 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:QlikviewimagesStarSchemaProduct_dimension.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); LOAD TimeID, Year, Month FROM [C:QlikviewimagesStarSchemaTime.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); LOAD SupplierID, SupplierName FROM [C:QlikviewimagesStarSchemaSuppliers.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); LOAD RegionID, Continent, Country FROM [C:QlikviewimagesStarSchemaRegions.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); LOAD ProductID, RegionID, TimeID, SupplierID, Quantity, DefectPercentage FROM [C:QlikviewimagesStarSchemaSupplier_quantity.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Star Schema Data Model 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. Print Page Previous Next Advertisements ”;

QlikView – Master Calendar ”; Previous Next 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. Input Data 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 Load Script We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file. 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. Create Master Calendar 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. Select Fields 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 Final Data The final output shows the table showing the Quarter and Month values, which are created using the Sales data and Master Calendar. Print Page Previous Next Advertisements ”;

Discuss QlikView ”; Previous Next QlikView is a leading Business Discovery Platform. It is very powerful in visually analyzing the relationships between data. It does in-memory data processing and stores the data in the report itself that it creates. It can read data from numerous sources including files and relational databases. It is used by businesses to get deeper insight by doing advanced analytics on the data they have. It even does data integration by combining data from various sources into one QlikView analysis document. QlikView is a leading Business Intelligence and Analytics Platform in Gartner Magic Quadrant. Print Page Previous Next Advertisements ”;

QlikView – RangeSum Function ”; Previous Next 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. Input Data Let us consider the monthly sales figure as shown below. Save the data with file name monthly_sales.csv. Month,Sales Volume March,2145 April,2458 May,1245 June,5124 July,7421 August,2584 September,5314 October,7846 November,6532 December,4625 January,8547 February,3265 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from 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:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Applying RangeSum() Function 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:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above given script. Go to the menu Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below. Print Page Previous Next Advertisements ”;

QlikView – Concatenation ”; Previous Next Concatenation feature in QlikView is used to append the rows from one table to another. It happens even when the tables have different number of columns. It differs from both Join and Keep command, as it does not merge the matching rows from two tables into one row. Input Data Let us consider the following two CSV data files, which are used as input for further illustrations. Please note the second data set has an additional column named Country. SalesRegionOld.csv ProductID,ProductCategory,Region,SaleAmount 1,Outdoor Recreation,Europe,4579 2,Clothing,Europe,4125 3,Costumes & Accessories,South Asia,6521 4,Athletics,South Asia,4125 5,Personal Care,Australia,5124 6,Arts & Entertainment,North AMerica,1245 7,Hardware,South America,456 SalesRegionNew.csv ProductID,ProductCategory,Region,Country,SaleAmount 6,Arts & Entertainment,North AMerica,USA,1245 7,Hardware,South America,Brazil,456 8,Home & Garden,South America,Brazil,241 9,Food,South Asia,Singapore,1247 10,Home & Garden,South Asia,China,5462 11,Office Supplies,Australia,Australia,577 Load Script We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file. Then we edit the commands in the script to apply the concatenation between the tables. Next, we load the above data to QlikView”s memory and create a Table Box by using the menu Layout → New Sheet Objects → Table Box where we choose all the available fields to be displayed as shown below. Concatenated Data Completing above steps we get the Table box displayed as shown below. Please note the duplicate rows for the product ID 6 and 7. Concatenate does not eliminate the duplicates. Print Page Previous Next Advertisements ”;

QlikView – Column Manipulation ”; Previous Next Column Manipulation is a type of Data Transformation in which a new column is populated with values from an existing column, which meets certain criteria. The criteria can be an expression, which is created as part of the Data Transformation step. Input Data Let us consider the following input data, which represents the actual and forecasted sales figures. Month,Forecast,Actual March,2145,2247 April,2458,2125 May,1245,2320 June,5124,3652 July,7421,7514 August,2584,3110 September,5314,4251 October,7846,6354 November,6532,7451 December,4625,1424 January,8547,7852 February,3265,2916 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the “Table Files” option from the “Data from Files” tab and browse for the file containing the above data. After clicking Next, we choose the Enable Transformation Step button to carry out the required data transformation. Selecting the Data Transformation Choose the Column tab and then choose the New button. It asks to specify the New column and the Row Condition. We specify column 3 as the source column and pick the values, which start with two as the Row Condition. Transformed Data On completing the above steps, we get the transformed data as shown below. Load Script for Transformed Data The load script for the Transformed data can be seen using the script editor. The script shows the expression, which creates the new column with required values. Display Transformed Data The transformed data can be seen by creating a Table Box using the option in the menu Layout → New Sheet Object. Print Page Previous Next Advertisements ”;