topic Re: SQL query on selection in QlikView

SQL query on selection

Wed, 20 Jan 2016 13:34:26 GMT

Hi,

Let's say, I have a database with following columns:

Country, Province, City and Population.

Firstly, I want to download from server only list of Countries and Population of these Countries and display this on bar chart.

When user select some Country, I want to download from the base all Provinces from this Country and Population for these Provinces, and display these data on the same bar chart. The same procedure is with selection of City.

Shortly, I want to implement drill-down on database data.

Is something like this possible in QlikView?

If so, could someone tell me how to achieve this?

Thank you for all your help.

Regards,

Mateusz P

Re: SQL query on selection

Wed, 20 Jan 2016 16:50:27 GMT

All your requirements are possible in Qlikview.

First Load all the required data into QLikview.

Create chart objects to fulfill the user requirements with existing Qlikview objects.

Its not easy to tell in the post how to do it. At least please post some sample data so community will help if you have any roadblocks.

If you are new to Qlikview, please get a Qlikview consultant and you can learn from him or attend Qlikview training.

Re: SQL query on selection

Thu, 21 Jan 2016 07:58:28 GMT

Thanks for your answer.

Sorry, I don't have any sample data yet.

The problem is, I can't load all data into Qlikview at once, because there could be millions of records in my database. I want to first download list of the countries, with the main script. When user click one of the country in a chart (does a selection), I want Qlikview to send another SQL query to database to get list of all provinces of the selected country. Is that possible?

Regards,

Mateusz

Re: SQL query on selection

Siva_Sankar — Thu, 21 Jan 2016 08:09:27 GMT

Try the following doc.

Extracting data from Oracle using Qlikview.docx

Explained with a example and screenshots.

Regards,

Siva Sankar

Re: SQL query on selection

Anonymous — Thu, 21 Jan 2016 08:35:27 GMT

Yes, What ever you have asked, possible in Qlik..

Fetch all the data from your data source into QV first, and then see this?

Bar Chart - Drilled down of specific field

Creating Cycle and Drill Groups

Re: SQL query on selection

jagan — Thu, 21 Jan 2016 08:39:31 GMT

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.

Re: SQL query on selection

jagan — Thu, 21 Jan 2016 08:40:50 GMT

But it is not suggested, it will have performance issues. Instead you can load all the required data, if you required you can increase the reload frequency so that you always have latest data.

Regards,

Jagan.

Re: SQL query on selection

Thu, 21 Jan 2016 10:09:29 GMT

HI,Mateusz P

You have asked the question that is linked to a feature of Qlik View to work on Big Data called "Direct-Discovery".Though you can access Bigdata with out loading all the files to the In-memory of Qlik View there are many draw backs of this feature like you cant do SET ANALYSIS ,direct discovery measures and detail fields are not supported on global search. so,it is always preferable to lad all the data and process it.