QlikView / QlikSense : Extract - All / Limited Table from Databases : QVD Generator + CALENDAR Loop Extract

// QlikSesnse
// SET vDBLoad=1; SET vCountry='SG';
// SET vDBO='"ERP_PRD"';
// SET vConnection='ERPDB (qlik.admin)';
// SET vP1_QVDFolder='lib://Qlik_DataFolder (qlik_qlik.admin)/04 Data\QVD\$(vCountry)';
// LIB CONNECT TO '$(vConnection)';

// ALL Table:
Tables_Master:
   LOAD  '$(vDBO).dbo.'&name AS TBL,rows as NO_OF_Rows,name as TABLE_RAW,TABLE_TYPE
   ,if(SubStringCount(name,'$')=1, SubField(name,'$',1)) as Company
   ,if(SubStringCount(name,'$')=1, SubField(name,'$',2)) as TABLE_NAME
   ;
   SQL SELECT TBL.object_id, TBL.name name,INF_S.TABLE_TYPE, SUM(PART.rows) AS rows
   FROM $(vDBO).sys.tables TBL
   INNER JOIN $(vDBO).sys.partitions PART ON TBL.object_id = PART.object_id
   INNER JOIN $(vDBO).sys.indexes IDX ON PART.object_id = IDX.object_id
   INNER JOIN $(vDBO).INFORMATION_SCHEMA.TABLES INF_S on INF_S.TABLE_NAME=TBL.name 
   AND PART.index_id = IDX.index_id and TBL.type='U'
   WHERE IDX.index_id < 2 and INF_S.TABLE_TYPE='BASE TABLE'
   GROUP BY TBL.object_id, TBL.name,INF_S.TABLE_TYPE
   order by 3 desc;
STORE Tables_Master into [$(vP1_QVDFolder)\Tables_Master.qvd](qvd);
DROP Table Tables_Master;


// LIMITED Table:

Tables_Master:
LOAD * INLINE [
    TABLE_NAME
    G_L Account
     Store
    Dimension Value
]; 

// Extraction Script:
Set errormode=1;
For i=1 to FieldValueCount('TABLE_NAME')
 
LET vTableName=keepchar(upper(FieldValue('TABLE_NAME',$(i))),'ABCDEFGHIJKLMNOPQRSTUVWXYZ');
LET vdbTableName='$(vDBO_2)'&FieldValue('TABLE_NAME',$(i))&'"';
LIB CONNECT TO '$(vConnection)';

Trace $(vdbTableName);
$(vTableName):Load '$(vdbTableName)' AS Table_Name,*; SQL SELECT * FROM $(vdbTableName); 
STORE '$(vTableName)' INTO [$(vP1_QVDFolder)\$(vTableName).QVD](QVD);
DROP TABLE $(vTableName);

DISCONNECT;

Next i


Financial Calendar For Extract:

SET vFiscalYearStartMonth = 7;
LET vDateMin = Num(Date(MakeDate(2018,7,1)));
LET vDateMax = Num(Date(MakeDate(2020,12,31)));
LET vDateToday = Num(Today());

TempCalendar1:
LOAD Date($(vDateMin) + RowNo() - 1) AS Date 
AUTOGENERATE 1  WHILE $(vDateMin)+IterNo()-1<= $(vDateToday);

Master_Calendar:
Load Distinct Date(Date,'DD-MM-YY') as Date
   ,LEft(YearName(Date, 1, $(vFiscalYearStartMonth)),4) AS FiscalYear_Num  // Fiscal Calendar Year 
Resident TempCalendar1;
DROP Table TempCalendar1;

// Date Issue:  https://stackoverflow.com/questions/5505935/convert-from-datetime-to-int
FY_Calendar: LOAD FiscalYear_Num
,min(Date)-2 as Fiscal_StartDate
    ,max(Date)-2 as Fiscal_EndDate
Resident Master_Calendar Group by FiscalYear_Num Order by FiscalYear_Num;
DROP Table Master_Calendar;


//Calendar Loop:

For i=1 to FieldValueCount('FiscalYear_Num')
 
LET vFiscalYear=FieldValue('FiscalYear_Num',$(i));
LET vFiscalStart=FieldValue('Fiscal_StartDate',$(i));
LET vFiscalEnd=FieldValue('Fiscal_EndDate',$(i));

LIB CONNECT TO '$(vConnection)';

GLENTRY_Year:
LOAD [Entry No_] as GL_Entry_No,* ;
SQL SELECT * FROM $(vDBO_2)G_L Entry"
Where CONVERT(INT,[Posting Date])>=$(vFiscalStart) and CONVERT(INT,[Posting Date])<=$(vFiscalEnd)
and left([G_L Account No_] ,1) >=6;

DISCONNECT;
NEXT i
Drop Table FY_Calendar;

Creating Qlik Variables with Set Analysis in Script

SET vUnderYear ="=max(undyr)";// 2013
SET vRepStartYTDDate ="=Num(Floor(min({<undyr={'$(vUnderYear)'},Quarter=>}%Date_Date_Key)))";// 41275
SET vRepStartDate ="= Num(Floor(MonthStart(min({<undyr={'$(vUnderYear)'}>}%Date_Date_Key))))";// 41275
SET vRepEndDate ="=Num(Floor(MonthEnd(max({<undyr={'$(vUnderYear)'}>}%Date_Date_Key))))";// 41364
SET vPeriodSelected ="=GetSelectedCount(Year)+GetSelectedCount(Quarter)+GetSelectedCount(Month)+GetSelectedCount(undyr)+GetSelectedCount(CalendarYear)+GetSelectedCount(CalendarQuarter)+GetSelectedCount(CalendarMonthName)";// 2
SET vErrorMessage = 'Please select UWYR and Transaction Period' ;// Please select UWYR and Transaction Period

16 Sales Qualification Questions to Identify Prospects Worth Pursuing

Original Posts from : http://blog.hubspot.com/sales/16-sales-qualification-questions-to-identify-prospects-worth-pursuing

Thanks to Emma Brudner | @emmajs24

Not every lead is a good fit for a product or service -- no matter how strongly a salesperson believes they are (or wants them to be). Buyers don't buy just because they have a serious need, a looming deadline, or money to burn. They buy because of a combination of all of these factors, and more.
During the qualification call, salespeople can't simply focus on establishing a fit on one of these criteria. They have to establish a fit on all the relevant factors. While the specific sales qualification questions a rep asks will depend on the product or service they sell, here are 16 solid conversation starters that can help you recognize who's a successful customer in the making, and who's barking up the wrong tree.

1) What's the business problem you're seeking to fix with this offering?

Change isn't easy, and businesses don't undertake system overhauls and new implementations just for the fun of it. If there's no real problem the prospect is trying to solve, there's no real reason for them to buy. Establish pain (either from a known issue, or from a problem the prospect wasn't even aware of) before diving into other questions.

2) What's prompting you to do something about it now?

Prospects who have recently experienced a significant trigger event, such as a change in leadership, market shift, legal problem, or major company development, will have more incentive to address the issue now rather than later.

3) What has prevented you from trying to solve the problem until now?

Do other priorities keep taking precedent? Is there a bend in the path to a solution? Learning what has historically blocked the way to fixing this problem can help the salesperson understand where it falls on the list of priorities, as well as alert them to potential pitfalls.

4) What happens if you do nothing about the problem?

If the answer is "well, not much," the prospect doesn't have a pressing need. At this point, the salesperson should either disqualify the lead, or explain to them the danger that lies ahead if the problem goes unresolved (only if they truly believe this).

5) Do you have a budget allocated for this project? If not, when do you expect that you will?

Money isn't everything, but it certainly has bearing on whether or not a prospect is worth pursuing. The specific number doesn't matter as much as the fact that your offering's price and the prospect's ability to pay are within the same ballpark. For instance, if your product costs $1 million and the prospect can only afford $100, the sale isn't going to go through.

6) How does the budget signoff process work?

This question can uncover additional financial decision makers that need to be looped in sooner rather than later.

7) What are you currently spending on this issue?

If the prospect already has a competitive solution in place, the salesperson can get a benchmark of how much they're used to spending. And with a firm number, the salesperson can then ask if the prospect would be comfortable going higher.

Here's how HubSpot Sales VP Pete Caputa phrases this question in his sales qualification calls:

"We've established that your goal is X and that you're spending Y now to try and achieve X. But it's not working. In order to hire us, you will need to invest Z. Since Z is pretty similar to Y and you're more confident that our solution will get you to your goal, do you believe it makes sense to invest Z to hire us?"

8) How would the decision process work with an offering like this? What would be your role in the process, and the roles of others on the decision team?

Is the person you're talking to the decision maker? Or is the decision maker someone else? Make sure you understand the dynamics of the buying committee and who has authority over what. For example, while one stakeholder could be the "ultimate" signer, another might be the financial approver.

9) Has your company ever considered/used a product like this before? If so, what happened?

The best way to make sure you don't repeat history is to study it. Compare your buyer's expectations and perceptions of "good" and "bad" to your offering. If there's a significant mismatch, it's best to disqualify the prospect now before you spend any more time on the deal.

10) What hurdles could crop up and derail this project?

Too many potential potholes might not make the deal worth pursuing.

11) What other solutions are you evaluating?

In some cases, an additional vendor is brought in after a prospect has already decided on another in the name of due diligence, or to put price pressure on the incumbent provider. Listen carefully to the prospect's answer to assess whether their engagement with you isn't totally authentic.

12) What does success look like to you, both in terms of qualitative and quantitative results?

Whether a prospect becomes a happy customer or a detractor largely depends on their expectations. If their definition of success does not line up with what your offering can provide, it might be time to disqualify.

13) What does solving this problem mean to you personally? What do you stand to gain if the issue was solved? What do you stand to lose if it goes unresolved?

An internal champion can often be more effective at corralling the buying committee around a particular product or service than the salesperson herself. The more skin the prospect has in the game, the more likely they are to be a helpful advocate.

14) Based on what you've seen so far, do you think our offering could be a viable solution for your problem?

Peppering mini agreements or commitments throughout the buying process -- even in the qualification call -- can lay the groundwork for the ultimate agreement at the end.

15) When do you need a solution in place by?

If there's a drop dead date that the problem absolutely needs to be fixed by, the salesperson can then work backward to determine a signing date.

16) Do you agree that the next step is X by Y date? When would be a good time on or around that date to schedule a call or meeting?

This isn't so much a qualification question as a sales best practice. Every sales interaction should end with a next step tied to a date. If the buyer is truly committed to moving forward, they won't have any trouble agreeing to a second meeting or call. Interest: secured; prospect: qualified.

Qlikview-Interview-Questions

Thanks To "intellipaat" for getting this wonderful collection through LinkedIn.
Original Post : 
https://intellipaat.com/interview-question/qlikview-interview-questions/?utm_source=link%2FqlikviewIQ_AS&utm_medium=posting&utm_campaign=link%2FqlikviewIQ_AS%20posting

1.What kind of chart we use in Qlikview?

We generally uses bar chart, line chart, combo chart, scatter chart, grid chart, etc.

2.Explain Set analysis in qlikview ??

It is used for set of groups. Mostly used in arrgeted function like sum (year)etc.

3.Define Trellis chart?

In Trellis chart we can create array of chart based on first dimension. Bitmap chart are also made of trellis display.

4Explain Mini Chart?.What do you mean by sub reports and how we can create them?

With the help of Mini Chart we can set type of modes instead of values in table mode. We can also change the colors.

5.What is Pivot Table?

Pivot Table:
A pivot table is better at the time of grouping. We can also show pivot table like a cross table which is a beneficial feature. But there is one disadvantage of it which is if we have to sort a pivot table than we have to sort it first according to the first dimension then to the next.

6.Which graph we will use for two years difference sale ?

BAR Graph we will use.

7.What is Straight Table?

A straight table is much better at the time of sorting as compared to the pivot table as we can sort it according to any column as per our choice. But it is not good for grouping purpose.

8.How many dimensions we can use in Bar chart?

We can use only two dimension

9.Which Qlikview object has only expression and no dimension?

Gauge chart and list box have only expression and no dimension.

10.How we can use Macros in our application?

We can use macros for various purposes like for reloading the application and to create a object.

11.What do you understand by layers in Qlikview?

The layer are basically set on the sheet object properties layout where bottom, top, normal respective to the number -1,0 and 1.

12.What is Dimensions?

Dimensions allow data examination from various perspectives.

13.Explain about Normalized Data?

Well Structured Form of Data, which doesnt have any repetition or redundancy of data. Its a kind of Relational data. Its mainly used in OLTP kind of stuffs Denormalized Data – Its a whole bunch of data without any relationship among themselves, with redundancy of data. Its mainly used in OLAP kind of stuffs.

14.What Is Star Sechma ?

The simplest form of dimensional model, in which data is prearranged into facts and dimensions is known as Star schema.

15.What is Snowflaking Schema ?

A snowflake schema is a difference of the star schema. Snowflaking is used to improve the presentation of particular queries.

16.Explain interval match?

The internal match is prefixes with the load statement which is used for connecting different numeric values to one or more numeric interval.

17.Explain internal match function()?

Internal match fuction is used to generate data bucket of different sizes.

18.What is Container ?

A container object is used to keep multiple charts. We can use a container object to keep many charts in the same box.

19.What do you understand by extended interval match function()?

Extended interval match function() is used for slowly changing the dimensions.

20.what are the new features in QV 11?

Container Object;Granular Chart Dimension Control; Actions like, clear filed; meta data,etc are the new features in QV 11.

21.Explain joins and its types?

Join is used to convert the given data and whenever we are using joins for converting data its is known ad Data Merging.
It has many types:
a. Left join
b. Right join
c. Inner join, etc

22.What is Left Join?

Left join specifies that the join between the two tables should be left join, it uses before the word join. The resulting table only contain the combination among two tables with the full data set from the first table.

23.Define right join?

Right join specifies that the join between the two tables should be right join, it uses before the word join. The resulting table only contain the combination among two tables with the full data set from the second table.

24.Explain Inner Join?

Inner join specifies that the join between the two tables should be inner join. The resulting table should contain the full data set from both the sides.

25.What are modifiers?

Modifiesr deals with the Fields name.
For example: sum({$}Sales)
Returns the sales for current selection, but with the selection in “Region” is removed.

26.Explain Identifiers Syntax?

1. 0- Represents the empty set
2. 1- Represents the full sets of records
3. $-Represents the record of current selection
4. $1-Represents the previous selection
5. $_1-Represents the next selection
6. Bookmark01-Represents the Bookmark name

27.Explain 3-tier architecture of Qlikview Application?

1-tier: Raw data is loaded and we create QVD
2-tier: QVD is converted in business login and the requirement of business and data model is created.
3-tier: Reading all QVD from 2-tier and we make a single QVW.

28.How does Qlikview stores the data internally?

Qlikview stores the data in QVD as QVD has data compression capability. Qlikview has better performance than other BI because of its memory analytics approach.

29.Explain the restrictions of Binary load?

Binary Load can be used for only one application means we can only read the data from one QVW application and moreover set scripts is also a restriction.

30.Differentiate betwwen subset Ratio and Information Density.

Subset Ratio: It is used for easily spot problem in key field association.it is only relevant for key fields since they are present in multiple tables and do not share the same value.
Information Density: It is the field which contain the percentage of row which contain the non-null value.

31.what is the use of Optimized Load?

Optimized load is much faster and preferable especially for large set of data. It is possible if n o transformation are made at the time of load and no filtering is done.

32.Differentiate between keep and joins?

Keep and joins do the same functions but in keep creates the two tables whereas join only creates the one table. Keep is used before the load or select statements.

33.Define synthetic Key?

Synthetic key is the key where two or more tables consists more than one common column between them is called as synthetic key.

34.What is incremental load in Qlikview?

Incremental load is nothing but loading new or changed records from the database. With the help of QVD files we can use incremental Load.

35. Differentiate between set and let option in Qlikview??

Set: it assigns the variable without assesses the expression.
Let: it assigns the variable with assesses the expression.

36.Define Qlikview Resident Load.

Resident load is a part of loading data in Qlikview application. It is used for loading data in tables which is already loaded in Qlikview application.

37.How we can optimize QV application?

It can be optimized by creating the data into qvds. When complete qvw application is changed into qvd than this qvd will be store in the RAM.

38.What is mapping load?

Mapping load is used to create the mapping table that can be used for replacing field value and field names.

39.Define apply map.

apply map is used to add fields to the tables with the help of other tables. It can be used as joins.

40.What is concatenation?

It means sequence of interconnected things i.e. any column or row which is related to each other can be connected through concatenation.

41.Define NoConcatenation.

NoConcatenation prefix is used to forced the identical tables as two separate internal tables.

42.Define connect statement.

It is used to establish a connection the connection to database with the help of ODBC or OLEDB interface.

43.What do you understand by Fact constellation Schema?

It is a logical database structure of data Warehouse . It is design with the help of De Normalized Fact..

44.What do you mean by RDBMS?

It stands for relational Database management System. It arrange the data into respective column and rows.

45What do you understand by the term CAL in Qlikview?

Every client need a CAL to get connected with Qlikview Server. The CALS are taken up with Qlikview Server and tied with the server serial number.

46.Differentiate between QV server and publisher?

QV Server is a program that is installed on computer with various CALS which allow user to access QV Files on the server. Publisher is a program which manages centralized control on our QV files and manages them how and when the are loaded and distributed.

47.What do you understand by snapshot view of the table?

By this option we can see number o tables and related associations.

48.How we can bring data into qv?

We use ODBC, OLEDB, SAP connectors kind of data connections.

49.How we can handle Early Arriving Facts.

We can load data from ODBC, OLEDB, SAP connectors , by select statements and we can also load files like excel, word, etc. by using Table Syntax.

50.What type of data we generally use?

We use flat files, excels, QVDs, etc ad data.

51. Explain about QlikView?

QlikView is the Business Intelligence tool used by the University of St Andrews. Data from different University systems is combined and presented in a single dashboard in an easy and understandable way.

QlikView dashboards at the University of St Andrews are built on the following principles:

• Dashboards must be effective to use
• Dashboards must support users in carrying out their tasks
• Dashboards must provide the right kind of functionality
• It must be easy to learn how to use a dashboard
• It must be easy to remember how to use a dashboard
• To use QlikView, you do not need to have technical expertise in information systems, just a willingness to learn how it can support you.

52. What are the benefits of using QlikView?

As the name suggests, QlikView is a combination of quick and click and these features make it intuitive and easy to use. Users can visualize data, search multiple data sets, create ad hoc reports, and view patterns and trends in data that may not have been visible in other reports.
QlikView is
• Flexible – dashboards are web based and accessible from desktop computers and mobile devices
• Interactive – users are able to drill down and select particular data within charts or tables
• Usable – users can see large amounts of data effectively and efficiently
• Scalable – useful for multiple business processes at analytical, operational and strategic levels

53. How is QlikView 11 different from QlikView 10?

QlikView 11 brings new levels of capability and manageability to the QlikView Business Discovery platform. In this release, we focused our investments on five value propositions:

• Improve collaborative decision making with Social Business Discovery
• Gain new insights into opportunities and threats and relative business performance with comparative analysis
• Expand QlikView usage to additional devices, including smartphones, with mobile Business Discovery
• Enable a broad spectrum of users to jointly develop QlikView apps with QlikView’s rapid analytic app platform capabilities
• Improve the manageability and performance of QlikView with new enterprise platform capabilities.

54. What is QlikView comparative analysis?

Business users can quickly gain new kinds of insight when analyzing information in QlikView, with new comparative analysis options. App developers can now create multiple selection states in a QlikView app; they can create graphs, tables, or sheets based on different selection sets.

55. What mobile device platforms does QlikView 11 support?

QlikView 11 delivers mobile functionality for Apple iOS and Android tablets and smartphones. QlikView supports Android tablets when the following conditions are met:

• QlikView Server version 10 SR3 or later
• The native browser, not a downloaded one
• Currently our HTML5 web apps support only Apple and Android handhelds. Because many Black Berry are older devices that don’t fully support HTML5 (and many are non-touch), we don’t have a web-based solution for them at this time.

56. What is document-level auditing in QlikView 11?

New optional settings within QlikView Management Console enable administrators to more effectively audit user interactions. Administrators can audit QlikView usage not only at the system level (the entire QlikView Server), but down to the document level.

57. What are the key differences between QlikView and any other standard statistical software package (SAS, SPSS)?

Key difference is in terms of the database used. QlikView offers a quite simple visualization that matches the MS excel filtering. SAS is useful in case of Meta data while SPSS is good for analysis.
In comparison of the above three, QlikView is most user friendly and fast in terms of generating diverse dashboards/templates.
In terms of calculations, advanced statistics options are limited in QlikView.
For market research and analysis SPSS has direct facility algorithms.

58. What are QlikView annotations?

With the new annotations collaboration object QlikView users can engage in threaded discussions about QlikView content. A user can create notes associated with any QlikView object. Other users can then add their own commentary to create a threaded discussion. Users can capture snapshots of their selections and include them in the discussion so others can get back to the same place in the analysis when reviewing notes and comments. QlikView captures the state of the object (current selections), as well as who made each note and comment and when, for a lasting record of how a decision was made.

59. What are the main features of QlikView?

QlikView offers the following features:

• Dynamic BI Ecosystem
• Data visualization
• Interacting with dynamic apps, dashboards and analytics
• Searching across all data
• Secure, real-time collaboration

60. What are the differences among QlikView Server editions?

The differences are:

QlikView Server Enterprise Edition (EE) is available for customers looking to support a large number of users and integrate into enterprise environments. It includes features such as:
• Unlimited documents
• Integration with third party security systems
• Server clustering
• Small Business Edition (SBE) is designed to be used in smaller deployments.
It has the following limitations:
• For use only with Named and Document CALs
• Limited to 25 Named User CALs
• Limited to 100 Document CALs
• No support for additional servers
• Only supports Windows Active Directory to handle security and access control

Information Access Server (IAS) is an edition of QlikView Server designed to power public Internet sites. This edition:
• Includes the add-on QlikView Real Time Server
• Is licensed for uncapped number of user but limited to one QlikView document
• Must be set to anonymous mode only and authentication must be off
• Requires that the QlikView server be on the public Internet and publicly accessible
• Requires that the URL for accessing the site powered by the QlikView Server be publicly accessible
• Requires that no QlikView client (e.g., QlikView Desktop, Internet Explorer plug-in, Ajax) can access the QlikView Server (all user interfaces must be built by the customer manually or with QlikView Workbench)

QlikView Extranet Server (QES) is an edition of QlikView Server designed to extend QlikView functionality to external users via an extranet. QES:
• Requires authentication. Users must be external to the purchasing organization (customers, partners, etc.).
• Restricts server access to the Ajax client and mobile clients
• Provides the option to customize the QlikView application via the included QlikView Workbench
• Supports a maximum of 3 QlikView documents
• Supports session CALs and usage CALs only