Hi,
You can try Direct Query for this, refer Qlikview help file for details.
Direct Query
The DIRECT QUERY statement allows you to link tables through an ODBC or OLE DB connection using the Direct Discovery function.
DIRECT QUERY DIMENSION fieldlist [MEASURE fieldlist] [DETAIL fieldlist] ] [DETACH fieldlist]FROM tablelist
[WHERE where_clause]
The DIMENSION, MEASURE, and DETAIL keywords can be used in any order.
The DIMENSION and FROM keyword clauses are required on all DIRECT QUERY statements. The FROM keyword must appear before the DIMENSION keyword.
Note: The STALE keyword no longer exists in the Direct Query syntax. The caching limit to the Direct Discovery query results is now set by the DirectCacheSeconds script variable.
The fields specified directly after the DIMENSION keyword are loaded in memory and can be used to create associations between in-memory and Direct Discovery data.
Note:
The DIRECT QUERY statement cannot contain DISTINCT or GROUP BY clauses.
Using the MEASURE keyword, you can define which fields, besides the linked fields listed after the DIMENSION keyword, are available in QlikView. MEASURE fields are recognized on a "meta level." MEASURE field data resides only in the database.
The tablelist is a list of the names of tables or views in the database from which data will be loaded. Typically, it will be views that contain a JOIN performed on the database.
Typically, fields with discrete values that will be used as dimensions should be loaded with the DIMENSION keyword, whereas numbers that will be used in aggregations only should be selected with the MEASURE keyword.
DETAIL fields provide information or details, like "Comment" fields, that a user may want to display in a drill-to-details table box. DETAIL fields cannot be used in chart expressions.
DETACH fields are like DIMENSION fields except that association queries are not formulated when a DETACH is selected in a List Box or Chart. For example:
Direct Query
DIMENSION A
MEASURE X,Y,Z
DETACH B,C
From MySalesTable;
In this example, List Boxes for the B and C fields are grayed out; no green or white selections will show in those List Boxes. Only List Boxes or Charts with DIMENSION A are updated when selections are made.
Examples:
In this example, a database table called TableName, containing fields Dim1, Dim2, Num1, Num2 and Num3, is used.Dim1 and Dim2 will be loaded into the QlikView dataset.
DIRECT QUERY DIMENSTION Dim1, Dim2 MEASURE Num1, Num2, Num3 FROM TableName
Dim1 and Dim2 will be available for use as dimensions. Num1, Num2 and Num3 will be available to QlikView for aggregations. Dim1 and Dim2 are also available for aggregations. The type of aggregations for which Dim1 and Dim2 can be used depends on their data types. For example, in many cases DIMENSION fields contain string data such as names or account numbers.Those fields cannot be summed, but they can be counted: count(Dim1).
Note:
DIRECT QUERY statements are written directly in the Edit Script dialog box. They are not generated with the Create Select Statement dialog box the way SELECT statements are. To simplify construction of DIRECT QUERY statements, you can generate a SELECT statement with the Create Select Statement dialog box and then edit the generated script to change it into a DIRECT QUERY statement.
For example, the SELECT statement:
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;
Field lists
A field list is a comma-separated list of field specifications, fieldname {, fieldname}. A field specification can be a field name, in which case the same name is used for the database column name and the QlikView field name. Or a field specification can be a "field alias," in which case a database expression or column name is given a QlikView field name.
Field names can be either simple names or quoted names. A simple name begins with an alphabetic Unicode character and is followed by any combination of alphabetic or numeric characters or underscores. Quoted names begin with a double quotation mark and contain any sequence of characters. If a quoted name contains double quotation marks, those quotation marks are represented using two adjacent double quotation marks.
QlikView field names are case-sensitive. Database field names may or may not be case-sensitive, depending on the database. Direct Discovery Query preserves the case of all field identifiers and aliases. In the following example, the alias "MyState" is used internally to store the data from the database column "STATEID."
DIRECT QUERY Dimension STATEID as MyState Measure AMOUNT from SALES_TABLE;
This differs from the result of an SQL Select statement with an alias. If the alias is not explicitly quoted, the result contains the default case of column returned by the target database. In the following example, the SQL Select statement to an Oracle database creates "MYSTATE," with all upper case letters, as the internal QlikView alias even though the alias is specified as mixed case. The SQL Select statement uses the column name returned by the database, which in the case of Oracle is all upper case.
SQL Select STATEID as MyState, STATENAME from STATE_TABLE;
To avoid this behavior, use the LOAD statement to specify the alias.
Load STATEID as MyState, STATENAME;
SQL Select STATEID, STATEMENT from STATE_TABLE;
In this example, the "STATEID" column is stored internally by QlikView as "MyState."
Most database scalar expressions are allowed as field specifications. Function calls can also be used in field specifications. Expressions can contain constants that are boolean, numeric, or strings contained in single quotation marks (embedded single quotation marks are represented by adjacent single quotation marks).
Examples:
DIRECT QUERY DIMENSION SalesOrderID, RevisionNumber MEASURE SubTotal AS "Sub Total" FROM AdventureWorks.Sales.SalesOrderHeader
DIRECT QUERY DIMENSION "SalesOrderID" AS "Sales Order ID" MEASURE SubTotal,TaxAmt,(SubTotal-TaxAmt) AS "Net Total" FROM AdventureWorks.Sales.SalesOrderHeader
DIRECT QUERY DIMENSION (2*Radius*3.14159) AS Circumference, Molecules/6.02e23 AS Moles MEASURE Num1 AS numA FROM TableName
DIRECT QUERY DIMENSION concat(region, 'code') AS region_code MEASURE Num1 AS NumA FROM TableName
Direct Discovery does not support using aggregations in load statements. If aggregations are used, the results are unpredictable. A load statement such as the following should not be used:
DIRECT QUERY DIMENSION stateid, SUM(amount*7) AS MultiFirst MEASURE amount FROM sales_table
The SUM should not be in the load statement.
Direct Discovery also does not support QlikView functions in Direct Query statements. For example, the following specification for a DIMENSION field results in a failure when the "Mth" filed is used as a dimension in a Chart:
month(ModifiedDate) as Mth
Native data-source syntax
By design, the DIRECT QUERY statement is data-source neutral for data sources that support SQL. For that reason, the same DIRECT QUERY statement can be used for different SQL databases without change. Direct Discovery generates database-appropriate queries as needed.
Native data-source syntax can be used when the user knows the database to be queried and wants to exploit database-specific extensions to SQL. Native data-source syntax is supported:
- As field expressions in DIMENSION and MEASURE clauses
- As the content of the WHERE clause
Examples:
DIRECT QUERY
DIMENSION Dim1, Dim2
MEASURE
NATIVE ('X % Y') AS X_MOD_Y
FROM TableName
DIRECT QUERY
DIMENSION Dim1, Dim2
MEASURE X, Y
FROM TableName
WHERE NATIVE ('EMAIL MATCHES "\*.EDU"')
Table names
The database table name specified with the FROM keyword consists of one or more field names, separated by periods. Both simple and quoted names are allowed. Whether or not the table name is case-sensitive depends on the database.
Where clauses
The full syntax of database WHERE clauses is not defined here, but most SQL "relational expressions" are allowed, including the use of function calls, the LIKE operator for strings, IS NULL and IS NOT NULL, and IN. BETWEEN is not included.
NOT is a unary operator, as opposed to a modifier on certain keywords.
Examples:
WHERE x > 100 AND "Region Code" IN ('south', 'west')
WHERE Code IS NOT NULL and Code LIKE '%prospect'
WHERE NOT X in (1,2,3)
This last example cannot be written as:
WHERE X NOT in (1,2,3)
Restricted Terms
The following terms are used as keywords and so cannot be used as column or field names without being quoted:
- and
- as
- detach
- detail
- dimension
- distinct
- from
- in
- is
- like
- measure
- native
- not
- or
- where
Direct Discovery System Variables
DirectCacheSeconds
You can set a caching limit to the Direct Discovery query results for Charts. Once this time limit is reached, QlikView Server clears the cache when new Direct Discovery queries are made. QlikView queries the source data for the selections and creates the cache again for the designated time limit. The result for each combination of selections is cached independently. That is, the cache is refreshed for each selection independently, so one selection refreshes the cache only for the fields selected, and a second selection refreshes cache for its relevant fields. If the second selection includes fields that were refreshed in the first selection, they are not updated in cache again if the caching limit has not been reached.
The Direct Discovery cache does not apply to Table Boxes. Table Box selections query the data source every time.
The limit value must be set in seconds. The default cache limit is 1800 seconds (30 minutes).
The value used for DirectCacheSeconds is the value set at the time the DIRECT QUERY statement is executed. The value cannot be changed at runtime.
SET DirectCacheSeconds=1800
DirectConnectionMax
You can do asynchronous, parallel calls to the database by using the connection pooling capability. The load script syntax to set up the pooling capability is as follows:
SET DirectConnectionMax=10
The numeric setting specifies the maximum number of database connections the Direct Discovery code should use while updating Sheet objects. The default setting is 1.
Notes:
Caution should be taken when setting this variable. Setting it to greater than 1 is known to cause problems when connecting to MS SQL Server.
In the earlier release of Direct Discovery, this variable was named "LinkedConnectionMax" and the default was 4.
DirectUnicodeStrings
Direct Discovery can support the selection of extended Unicode data by using the SQL standard format for extended character string literals (N’<extended string>’) as required by some databases (notably SQL Server). The use of this syntax can be enabled for Direct Discovery with the script variable DirectUnicodeStrings.
Setting this variable to 'true' will enable the use of the ANSI standard wide character marker “N” in front of the string literals. Not all databases support this standard. The default setting is 'false'.
DirectDistinctSupport
When a DIMENSION field value is selected in a QlikView object, a query is generated for the source database (see Differences between Direct Discovery and In-Memory Data). When the query requires grouping, Direct Discovery uses the DISTINCT keyword to select only unique values. Some databases, however, require the GROUP BY keyword. Set DirectDistinctSupport to "false" to generate GROUP BY instead of DISTINCT in queries for unique values.
SET DirectDistinctSupport=false
If DirectDistinctSupport is set to true, then DISTINCT is used. If it is not set, the default behavior is to use DISTINCT.
Direct Discovery Character Variables
DirectFieldColumnDelimiter
You can set the character used as the field delimiter in Direct Query statements for databases that require a character other than comma as the field delimiter. The specified character must be surrounded by single quotation marks in the SET statement.
SET DirectFieldColumnDelimiter= '|'
DirectStringQuoteChar
You can specify a character to use to quote strings in a generated query. The default is a single quotation mark. The specified character must be surrounded by single quotation marks in the SET statement.
SET DirectStringQuoteChar= '"'
DirectIdentifierQuoteStyle
You can specify that non-ANSI quoting of identifiers be used in generated queries. At this time, the only non-ANSI quoting available is GoogleBQ. The default is ANSI. Uppercase, lowercase, and mixed case can be used (ANSI, ansi, Ansi).
SET DirectIdentifierQuoteStyle="GoogleBQ"
For example, ANSI quoting is used in the following SELECT statement:
SELECT [Quarter] FROM [qvTest].[sales] GROUP BY [Quarter]
When DirectIdentifierQuoteStyle is set to "GoogleBQ", the SELECT statement would use quoting as follows:
SELECT [Quarter] FROM [qvTest.sales] GROUP BY [Quarter]
DirectIdentifierQuoteChar
You can specify a character to control the quoting of identifiers in a generated query. This can be set to either one character (such as a double quotation mark) or two (such as a pair of square brackets). The default is a double quotation mark.
SET DirectIdentifierQuoteChar='YYYY-MM-DD'
DirectTableBoxListThreshold
When Direct Discovery fields are used in a Table Box, a threshold is set to limit the number of rows displayed. The default threshold is 1000 records. The default threshold setting can be changed by setting the DirectTableBoxListThreshold variable in the load script. For example:
SET DirectTableBoxListThreshold=5000
The threshold setting applies only to Table Boxes that contain Direct Discovery fields. Table Boxes that contain only in-memory fields are not limited by the DirectTableBoxListThreshold setting.
No fields are displayed in the Table Box until the selection has fewer records than the threshold limit.
.png "Luminary Alumni"){kind=icon}