topic QV/SQL database query in QlikView

QV/SQL database query

Fri, 08 Jul 2016 22:20:48 GMT

Hello,

I have a concern regarding the data delivered by QlickView app and would like to match QV returned output with database SQL query result.

Question: how can I get the SQL statement built by QV (including data load portion of it) in order to run it directly against DB through any Development tool (Oracle SQL Developer for instance).

Appreciated in advance,

Leon

Re: QV/SQL database query

rwunderlich — Fri, 08 Jul 2016 22:45:00 GMT

Any SQL statements used to load the data will be present in the Load Script. You should be able to see them there.

Chart computations do not use anything like SQL, so there is no equivalent to see.

-Rob

Re: QV/SQL database query

Fri, 08 Jul 2016 23:25:28 GMT

Thank you, Rob. I really didn't expect any respond on Friday evening.

Keeping in mind that I am still learning this stuff, I would appreciate if you can point me to the 'Load Script' functionality for the complete report if you don't mind (I checked all .qvw available menus, but couldn't find it). If it is not an option, I'll research the books.

Appreciated for your help.

Leon

Re: QV/SQL database query

marcus_sommer — Sun, 10 Jul 2016 05:03:18 GMT

Within the editor you could connect to a database and select the needed fields per wizard. Here a few posting which will be helpful for you:

The Great ODBC Confusion

Connectivity to Oracle Database

Preceding Load

- Marcus

Re: QV/SQL database query

rwunderlich — Mon, 11 Jul 2016 20:54:48 GMT

Hi Leon,

After opening the qvw in Qlikview desktop, you can view the load script by opening the script editor from the menu: File, Edit Script ( Ctrl e). Note you can only open the script when the file is opened from the file system using Qlikview desktop. You cannot view the script if the app had been opened from Access Point using the browser.

-Rob

Re: QV/SQL database query

Mon, 11 Jul 2016 22:21:12 GMT

Rob,

I already have tried that, but the way it is setup by my developer is the simple code in the File >’Edit Script’ where he is gathering all variable definitions from external file (and I do have a content of this file):

Formulas:

LOAD ReportName,

Variable,

Expression,

Description

FROM

(biff, embedded labels, table is Sheet1$);

Let vNoOfRows= NoOfRows('Formulas');

FOR i=0 to (vNoOfRows-1)

LET vVariable_Name = Peek('Variable',i,'Formulas');

LET $(vVariable_Name) = Peek('Expression',i,'Formulas');

I understand how it works, but it is not exactly what I am looking for.

As I mentioned, I have a concern regarding the data output from QV report and would like to run the COMPLETE query it creates on background against the database directly through any development tool.

This is an option in other BI tools (Cognos for instance) and I am just wondering if QV allowing to do that. Is it?

Marcus,

These documents are very helpful, but I already figured out the way to establish the database connection even it is not described in QV deployment materials.

Thank you for your help, guys!

Leon

Re: QV/SQL database query

rwunderlich — Tue, 12 Jul 2016 01:06:11 GMT

Hi Leon

There is no "background query" involved. QV works differently than other tools in that it does not issue any queries to a backend database to produce analysis. So there is no SQL to examine.

It does however, pre load all the data from database or files via the load script. The load script you posted looks incomplete. I'm guessing your load script has multiple tabs and you are only looking at the last tab of the script.

-Rob

Re: QV/SQL database query

Tue, 12 Jul 2016 01:26:09 GMT

Rob,

It is correct, I provided a content of one ‘Import Formulas’ tab only with impression that two more tabs ‘Main’ and ‘Section Access’ are not relevant to the subject discussing.

I guess I am too fresh to QlikView in order to discuss details, but I do understand what you are saying.

Thank you for clarification!

Leon