topic Re: What is Direct Query? What are the advantages? in QlikView

What is Direct Query? What are the advantages?

Tue, 05 Aug 2014 08:00:52 GMT

Can someone throw some light on Direct Query and elaborate with some example? Thanks in advance.

Warm Regards,
Dawar

Re: What is Direct Query? What are the advantages?

MK_QSL — Tue, 05 Aug 2014 08:03:28 GMT

If the dataset in a database is large, it is possible to load aggregated query results into QlikView without loading the complete dataset into the QlikView data model using the Direct Discovery function.

You can have more information by searching under Help -- Contents or reading QlikView Reference Manual...

Re: What is Direct Query? What are the advantages?

sujeetsingh — Tue, 05 Aug 2014 08:06:14 GMT

http://community.qlik.com/servlet/JiveServlet/previewBody/3710-102-1-4293/QlikView%2011%202%20Direct%20Discovery%20Techn…

You will get a complete details from this document.

Re: What is Direct Query? What are the advantages?

sujeetsingh — Tue, 05 Aug 2014 08:07:03 GMT

QlikView determines which data resides in-memory and which data is direct discovery data by

using the special script syntax, “DIRECT SELECT”. This syntax allows certain data elements not to

be loaded into the QlikView data model during the script reload process, but still available for

query purposes from the QlikView User Interface and to be combined for analysis with the

QlikView in memory dataset.

Once the direct discovery structure is established, the direct discovery fields can be used with

certain QlikView objects. When a direct discovery field is used on the QlikView object, QlikView

will automatically create the appropriate SQL query to run on the external data source. The

result of the query will be displayed on the QlikView object. When selections are made on the

QlikView application, the associated data values of the direct discovery fields will be used in the

WHERE conditions of the queries. With each selection, the direct discovery charts will be

calculated, where the calculations and aggregations will be done on the source table by

executing the SQL query created by QlikView. It is possible to use calculation condition feature of

the QlikView charts to set a condition indicating when the chart should be calculated. Until that

condition is met, QlikView will not run queries and the chart will not be calculated. Please note

that QlikView will execute SQL queries on the data source for some of the list boxes that use

direct discovery fields. This is required to achieve the associative navigation capability.

Re: What is Direct Query? What are the advantages?

maxgro — Tue, 05 Aug 2014 08:18:15 GMT

QlikView Direct Discovery FAQ 11.2 SR7.pdf

Re: What is Direct Query? What are the advantages?

ashfaq_haseeb — Tue, 05 Aug 2014 08:19:57 GMT

HI,

have a look at the attached document

Regards

ASHFAQ

Re: What is Direct Query? What are the advantages?

Wed, 08 Apr 2015 09:52:28 GMT

I don't see any real advantage.

It just used another limited syntax - I can't use UNION ALL, RIGHT JOIN, etc.
less filtering conditions
- my advanced DATE filter '=DATE>date#('01/01/2015') AND DATE<date#('01/04/2015')' translated to
  WHERE Date IN ('01/01/2015', '04/01/2015'...)
- text filter '=COMMENT LIKE 'Manifest*'' translated to
  WHERE COMMENT IN ('Manifest 1', 'Manifest 2', ...)
less aggregation formulas - only Sum(), Avg()...
still need reloads to refresh dimensions, detail, detach

Re: What is Direct Query? What are the advantages?

engishfaque — Wed, 08 Apr 2015 10:01:37 GMT

Dear Haimanta,

The DIRECT QUERY statement allows you to link tables through an ODBC or OLE DB connection using the Direct Discovery function.

Syntax:

DIRECT QUERY DIMENSION fieldlist [MEASURE fieldlist] [DETAIL fieldlist] ] [DETACH fieldlist]FROM tablelist

[WHERE where_clause];

Note 1: The DIMENSION, MEASURE, and DETAIL keywords can be used in any order.

Note 2: The DIMENSION and FROM keyword clauses are required on all DIRECT QUERY statements. The FROM keyword must appear before the DIMENSION keyword.

Note 3: The DIRECT QUERY statement cannot contain DISTINCT or GROUP BY clauses.

Example 1:

Direct Query
DIMENSION A
MEASURE X,Y,Z
DETACH B,C
From MySalesTable;

Example 2:

SQL SELECT

SalesOrderID,

RevisionNumber,

OrderDate,

SubTotal

TaxAmt

FROM AdventureWorks.Sales.SalesOrderHeader;

could be changed to the following DIRECT QUERY statement:

DIRECT QUERY

DIMENSION

SalesOrderID,

RevisionNumber,

MEASURE

SubTotal

TaxAmt

DETACH

OrderDate,

FROM AdventureWorks.Sales.SalesOrderHeader;

Note 4: DIRECT QUERY statements are written directly in the Edit Script dialog box. They are not generated with the Create Select Statement.

Reference: QlikView Help

Kind regards,

Ishfaque Ahmed

Re: What is Direct Query? What are the advantages?

Anonymous — Tue, 24 Nov 2015 08:56:00 GMT

Thanks alot Ishfaque. this make me understand Direct Query.

Re: What is Direct Query? What are the advantages?

reddy-s — Fri, 25 Dec 2015 15:56:07 GMT

Check this out. Should be help full.

Direct Discovery in Qlikview