(Qlikview developer, Designer and admin FAQ’S)
1. Difference between Set and Let?
Set : used to assign/concatenate text values
Let : used to assign expressions
2. Difference between Sum() and RangeSum()?
Sum : Sum(field name)
RangeSum(expr1 [ , expr2, ... exprN ]) Returns the sum of a range of 1 to N arguments.3. Difference between Info Load and Bundle Info?
As opposed to the + operator, rangesum will treat all non-numeric values as 0. The argument expressions of this function may contain Chart Inter Record Functions with a third optional parameter, which in themselves return a range of values.
RangeSum (1,2,4) returns 7
RangeSum (1,'xyz') returns 1
RangeSum (null( )) returns 0
RangeSum (above(count(x),-1,3)) returns the sum of the three results of the count(x) function evaluated on the row below the current row, the current row and the row above the current row.
RangeSum(Above(Sum(Amount),0,RowNo())) In this your accumulation starts from 1st value which it writes as it is. because you write the 0 for that value and then for others value in row it accumulates.
RangeSum(Above(Sum(Amount),1,RowNo())) in this your cumulative starts from 2nd row and it will make first row as zero.
RangeSum(Above(Sum(Amount),-1,RowNo())) in this case it pick up 2nd value for accumulation means takes two values to be accumulated in my example first it take 20 then 20+30 acc. to condition and add 20 above gives 70 then it gives you then it takes 70 accumulate then 30 and 50 gives you 150 and then gives 110 because of adding 50 and 60 and then 60 so on…
Info Load: used to link the external resources (Ex: Images, media files…) to the Internal data table.
Bundle Info: Purpose is same as Info Load
Preferred when changes are expected at external files location
1st time link the data from external files location and next time onwards it will refer from memory
4. Difference between Internal Table Viewer and Source Table Viewer
o Internal Table View is the default view. It shows the data tables as QlikView stores them. Composite synthetic keys are formed in tables that share more than one field. Synthetic tables are used to link them. This view offers the best understanding of the QlikView logic and also provides a very clean layout where each pair of tables has a maximum of one connector between them.5. Difference between Recno() and Rowno()?
o Source Table View shows data tables as QlikView reads them. Here there are no synthetic fields or synthetic tables. Composite keys are represented by multiple connectors between tables.
The RecNo() function will return an integer for the record currently being read from the source table starting with number one. The RowNo() function also returns an integer but it represents the position of the row in the resulting QlikView internal table.
So while RecNo() counts from the source table, RowNo() counts from the resulting table.
The RecNo field indicated the order in which the records were read from the source table and the RowNo field indicates the position of the record in the internal table.
6. Difference between P() and E()?
o Both functions p () and e () have the same syntax:
o p () returns the possible values, the other e () returns the excluded values.
7. Difference between ApplyMap() and Lookup()?
8. Difference between ValueList() and ValueLoop()?
9. Difference between Peek(),Pick() and Previous?
10. Difference between Pick and Match?
o The Pick function will return the expression/value corresponding to the expression that matches the first parameter. For example, Pick(2, ‘A’, ‘B’, ‘C”) will return B because B is the second expression and Pick(Number, Sum(1+1), Sum(2+2), Sum(3+3), Sum(4+4), Sum(5+5)) will return 6 if Number = 3. This function is excellent when you want to generate a random field value, e.g.
11. Ragged Hierarchies in Data warehousing
12. What are slowly changing dimensions?
13. Have you used Alternate states and why?
• Most businesses need comparative analysis to identify the state of the business. QlikView Alternate States was introduced in version 11 to facilitate comparative analysis such as year-over-year sales comparison where users want to see how current year sales compares to the previous year or current quarter compares to the same quarter a year ago.
• Most of the cases using only Line chart or bar chart using QV.
14. What is Set Analysis (benefits of using it)? Why it’s called Set Analysis
15. How to connect to Database?
o By using Connect button (under Data tab) from script window using ODBC/OleDb connector
o Select DSN (data source name)
o Select button to load the tables from database
16. What are the various data sources for QlikView?
RDBMS – MS Access, Sql Server, Oracle….
o Data Files
Raw data files : .xlsx,.xls, .csv,.txt, .xml
QlikView files : .qvd
o Web Files : Web services…
o Salesforce,... using third party connectors
17. What is partial reloading?
18. What care should be taken while designing a data model?
19. What are the two main techniques of combining Fact Tables, and the differences between the two?
20. How can you work with Slowly Changing Dimensions in QlikView?
21. How can you organize an efficient Delta Load, in a variety of data scenarios?
22. How should you structure your code using subroutines and functions?
23. Where do you create Dimension and Measure tags? How are they useful?
24. What is concept of Alternate States? Syntax for using in Set Analysis?
25. What are the different File Functions and QVD functions?
26. What are Value List and Value Loops?Tell me with scenarios?
27. What is Rand() ,Autonumber() and Autogenerate()?
28. What are Circular References?How do you avoid them?
29. What are Information Density and Subset Ratio?
30. Explain me with scenarios of Primary Key and Perfect Key?
31. What is First Value and FirstSorted Value()?
32. Tell me how to use Qualify and UnQualify?
33. What are the different Error Modes? What are the different Script Errors?
34. Have you Used Bubble Sort? In which scenario we should use?
35. What is Aggr() and Dual() ?
36. Why Sum() is better than Count()?
37. List some Better Practices that you have used in your Work?
38. Have you worked on reading Done Files? How do we handle them?
39. Tell me how can we work Insert, Update and Delete on Incremental Loads?
40. What is 3 Value Logic in QlikView? Can you elaborate in Detail?
41. Have you used Include and Config files? Can you write sample scripts for each?
42. Explain about the Section Access in brief and important setups we need to do?
43. What is Custom Sort and have you ever tried to implement?
44. What is the RAM Size and Processor of your RAM? What is the Application Size?
45. Have you implemented Waterfall Chart? How do we implement them?
46. Explain me how do we connect to OLEDB Connection?
47. What is Custom Format in QlikView?
48. If I Want to Skip specific rows in Excel during Load how do we need to do that?
49. Brief me about Set Analysis? Tell me script to compare the Sales of this year to Last Year?
50. Explain me about Source and User Documents in QMC?
51. Explain me some steps to improve Performance in Scripting and GUI?
52. What are the Data Types available in QlikView?
o There are no data types in QlikView :)53. Functions in Qlikview
o RecNo() – the record number of the input record
o RowNo() – the record number of the output record
o Rand() – a generator of random numbers,
o Ceil() – round upwards to nearest integer,
o Pick() – pick a specific value in a list of values
54. Load statement in different ways:
a) Load … From File ; (from file)55. How to display a list box as a horizontal list box?
b) Load … Resident Table ; (from an already loaded table or from in-memory tables or internal tables)
c) Load … Inline InlineTable ; (from a table written in the script)
d) Load … Autogenerate Number ; (from nothing)
o But with autogenerate there is no source – the records are generated and all field values must be derived from functions like Rand() or RecNo(). The number of records is specified in the number after the Autogenerate keyword.
o List Box --> Properties --> Go to Presentation Tab --> UnCheck Single Column
o You can also set the number of columns you want to view horizontally buy checking the Fixed Number of Columns tab and entering a value in the associated box.
o "Order by Column" needs to be unchecked (optional)
56. QlikView vs QlikSense
o QlikView is a tool for situations where you want prepared business applications, i.e. applications created by developers who put a lot of thought into the data model, the layout, the charts and the formulae; and deliver the applications to end-users who consume the applications. We call this Guided Analytics. The end-user has total freedom to explore data, select, drill down and navigate in the information, and can this way discover both questions and answers in the data. The end-user is however limited when it comes to creating new visualizations. This type of situation will without doubt be common for many, many years to come.
o Qlik Sense is a tool for situations where you don’t want to pre-can so much. Instead you want the user to have the freedom to create a layout of his own and in it, new visualizations; charts that the developer couldn’t imagine that the user wants to see. You want Self-service data discovery, which means a much more active, modern, engaged user. In addition, Qlik Sense is much easier to use when you have a touch screen, and is adaptive to different screen sizes and form factors. On the whole, Qlik Sense is a much more modern tool.
57. Optimized and unoptimized QVD Load Situations?
58. Best data modeling techniques
59. What are the common steps in every Data Modeling process?
60. Explain 3 tier architecture implementation while deploying QlikView application?
61. Briefly explain how does QlikView storage the data internally?
62. What are the restrictions of Binary Load?
o Only one Binary statement for the script and that should be initial statement
63. How are NULLS implemented in QlikView?
64. How do you optimize QlikView Application? (What tools are used and where do you start?)
o Whenever the tables contain huge data then we have to store it into QVD files (compressed format).
o If using large data base file try to create the incremental load for the QVD or load latest data only.
o Load only the required fields into QlikView from different data sources.
o Data Model: QlikView can analyze multiple data sources but works most efficiently with Star Schema or Snow flake Schema. The Star Schema Consists of few fact tables possibly only one ,referencing any no. of dimension tables
o User Interfaces:
Resource intensive expression like count(distinct) if() should be avoided. Set analysis should be used instead.
Complex script should be done in script rather than in charts/tables.
Macros should be replaced by built in QV functions.
Expressions which are used frequently in more than one chart/table should be stored in a variable which reduces the calculation time.
65. What is the difference between Subset ratio & Information Density?
66. What type of architecture are you using in your project
67. What type of schema are you using in your project? And why
o Star schema68. How many charts are you used till now
o Snow Flake Schema
69. Have you used macros in your project and why? No
70. Difference between QV versions?
• QMC and QEMC are taken as one QMC.
• Comparative analysis ( Alternative State)
• Expand QlikView usage to additional devices, including smart phones, with mobile Business Discovery.
• Granuler chart dimension is introduced in Qv11.
• Dimension Limits tab also included.
• QlikView 11 introduces important changes to the QlikView Publisher component.
• AccessPoint Redesign Helps IT Better Support Business Users.
• Container object enhancements
• Conditional enabling of dimensions and expressions improves the user experience.
• Annotations improve context and establish a record of how decisions were made.
71. Explain the QV Development Life Cycle till the User Visualizes the Dashboard?
72. Explain the Folder Structure we follow during the development and Folder Structure in QMC?
73. Explain EAV data modeling technique?
74. Difference between Star Schema & Snow Flake Schema?
75. Difference between RDBMS & Associative Database?
Refer for : QV FAQ's Part2