topic Re: SQL queries in Qlikview in QlikView

SQL queries in Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

Anyone able to help my implement an SQL query to my QlikView Dashboard?

I have done established ODBC connection in my script followed by SQL SELECT * FROM [PATIENT_ID] ORDER BY [SERVICE];

Nothing happens after reloading my script. Am i writing the SQL function wrong? I have even had people who know SQL better than I do say that certain codes I have tried should work and that Qlikview may require a different way to wite the SQL query. I ultimately want to be able to do a MINUS and UNION query for one specific task that I am hoping to finish by the end of next week.

Please let me know. Anything helps. Thank you

Re: SQL queries in Qlikview

vishsaggi — Fri, 08 Jul 2016 20:32:03 GMT

After you connect to the database did you use the Select button from script editor and try generating SQL script from there as Below:

Re: SQL queries in Qlikview

johnw — Fri, 08 Jul 2016 20:37:33 GMT

Can you execute this SQL outside of QlikView?

SELECT * FROM [PATIENT_ID] ORDER BY [SERVICE]

As far as doing that in QlikView, I would never do a select * except as a quick way to find out what fields are in an unfamiliar table. List the specific fields you need. I don't think it's strictly required, but you should also always have a LOAD statement before your SQL, and give it an explicit table name. So, something like this:

[Patients]:
LOAD *
;
SQL
SELECT
Something
,SomethingElse
,[Service]
FROM [PATIENT_ID]
ORDER BY [SERVICE]
;

I believe that all QlikView is doing with text between "SQL" and the final ";" is passing it through the ODBC connection with no interpretation or syntax checking at all. So if the SQL works outside of QlikView, it should work inside of QlikView, assuming your connection is good and all that sort of stuff.

Re: SQL queries in Qlikview

Mon, 11 Jul 2016 20:14:44 GMT

Dear vishsaggi,

Thank you for that information. I was not aware that Qlikview had a Select Statement tool available. But as I used the tool, although it did help the fact that I am not receiving any more 'Error in Script" messages when I would reload, but as I enter the SQL function, nothing happens after reloading. My entire script is as follows:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD PAT_ID,
CUST_NAME,
SERVICE,
PRICE
FROM
[SAMPLE TEST DATA 7 11 16.xlsx]
(ooxml, embedded labels);

ODBC CONNECT TO [Excel Files;DBQ=C:\Users\ksds698\Documents\SAMPLE TEST DATA 7 11 16.xlsx];

SQL SELECT `PAT_ID`,
PRICE
FROM `C:\Users\ksds698\Documents\SAMPLE TEST DATA 7 11 16.xlsx`.`Sheet1$`;

is there something wrong with the script? Or is there something I am missing?

Thanks,

Re: SQL queries in Qlikview

Mon, 11 Jul 2016 20:17:32 GMT

Dear John,

Thank you for that information. When I try running this, although the script does not produce an error, nothing happens to my current sheet, meaning the 2 columns that I commanded to show using SQL failed to show. Here is the script I have currently:

SQL SELECT `PAT_ID`,
PRICE
FROM `C:\Users\ksds698\Documents\SAMPLE TEST DATA 7 11 16.xlsx`.`Sheet1$`;

is there something wrong with the script? Or is there something I am missing?

Thanks,

Re: SQL queries in Qlikview

vishsaggi — Mon, 11 Jul 2016 20:43:57 GMT

Hello Tej,

Use

TableName:

Load *;

SQL SELECT 'Pat_ID',

Price

FROM ....... ;

Try this.

Re: SQL queries in Qlikview

johnw — Mon, 11 Jul 2016 20:48:24 GMT

I'm confused why you would use ODBC / SQL to load data from an Excel spreadsheet. I always just load the normal way, which is like the first load you have before the ODBC connection is even made.

Re: SQL queries in Qlikview

MarcoWedel — Mon, 11 Jul 2016 20:48:53 GMT

Running the script does not automatically create list boxes on your sheet.

Did you check the table viewer for loaded tables/fields?

regards

Marco

Re: SQL queries in Qlikview

vishsaggi — Mon, 11 Jul 2016 20:52:25 GMT

Sorry Tej, I was thinking completely out of blue. Why would you need a SQL statement when you are not pulling data from Excel. Please run the script as your first Load statement.

Thanks John, for catching this up.

However, can we know the exact requirement you are after ?

Thanks,
V.

Re: SQL queries in Qlikview

Peter_Cammaert — Mon, 11 Jul 2016 21:41:28 GMT

I'm confused as well (not very dfficult to do ...)

Why are you loading the same data set from an Excel file twice? Since you don't name your tables, the SQL SELECT will probably throw all rows together with the table from the previous LOAD ... FROM.

List boxes will only display each unique value once

Peter

Re: SQL queries in Qlikview

Tue, 12 Jul 2016 17:24:38 GMT

Hello Vishsaggi,

What I am hoping to do is to try out the SQL function in QlikView. Out of my QlikView table with all of my columns, if I were to enter an SQL function stating to select just 2 of those columns, only those two would show up on my sheet.

My ultimate goal for using Qlikview was to be able to run a MINUS query. I upload patient data to my Qlikview Dashboard every week and I wanted to be able to detect restatements in my data.

So as an example, I have 100 patient entries loaded to QlikView. Tomorrow, I want to load another 100 patients to QlikView. In this case I just add the new 100 to the same excel file linked to Qlikview and simply reload. Out of the new 100, 95 of them will be new patient entries, but 5 of them would be restatements, meaning the patient information has already been upload previously, but something changed in his data on file, causing it to be entered as a new entry.

To get rid of these restatements, I know I will be able to run a MINUS query because that is what is normally done, but it is done through Oracle. I want to be able to run this MUNIS query on QlikView. This would save me a lot of work and I know it can be done; it’s just a matter of knowing how.

I was hoping to use a Minus Query like the one below:

SELECT * FROM A

MINUS

SELECT * FROM B

UNION ALL

SELECT * FROM B

MINUS

SELECT ALL FROM A

Please let me know if you are able to help me run a MINUS query on QlikView.

Thank you for your help. It is truly appreciated.

-T

Re: SQL queries in Qlikview

johnw — Tue, 12 Jul 2016 17:49:04 GMT

What your talking about sounds like what we would call an incremental load. Each day you want to load all new or changed records from your data source, and combine these with all the existing records you have in QlikView, while throwing out the duplicates (the old version of the changed records).

A typical pattern for something like that would be something like this:

MyTable:
LOAD
UniqueKey
,OtherData
FROM wherever
WHERE record is new or changed
;
CONCATENATE (MyTable)
LOAD
UniqueKey
,OtherData
FROM MyTable.qvd (QVD)
WHERE not exists(UniqueKey)
;
STORE MyTable INTO MyTable.qvd (QVD);

You should be able to find lots of information on incremental loads on the forum and elsewhere.

Re: SQL queries in Qlikview

Tue, 12 Jul 2016 18:09:30 GMT

Thanks a lot John. This really helps and definitely sums up what I am trying to accomplish.

The only minor tweak I would add in if possible is to do everything that you mentioned except instead of throwing out the records, would those throw away records be able to be identified and maybe stored somewhere like in a separate chart? I just want to be able to identify those records being changed for reporting purposes, but still keep the newest, most updated record in QlikView as you said.

Again, I really appreciate the help and will look more into incremental loads on the forum and in my external resources.

Best,

Re: SQL queries in Qlikview

vishsaggi — Tue, 12 Jul 2016 18:24:25 GMT

Hello Tej,

This looks more like you are doing incremental load on the data.

Yes, in qlikview using some script statements we can use incremental load strategy for Insert, update and delete data from source tables into Qlikview tables.

Re: SQL queries in Qlikview

vishsaggi — Tue, 12 Jul 2016 18:27:17 GMT

Go through this link and try to implement your incremental logic based on your source excel file.

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Let us know if you face any further issues.

Thanks,
V.

Re: SQL queries in Qlikview

johnw — Tue, 12 Jul 2016 19:20:57 GMT

Maybe something like this, assuming loading once per day.

MyTable:
LOAD
Key
,Date
,OtherData
// do not load Current? flag
FROM MyTable.qvd (QVD)
;
CONCATENATE (MyTable)
LOAD
Key
,today() as Date
,OtherData
FROM wherever
WHERE record is new or changed
;
LEFT JOIN (MyTable)
LOAD
Key
,Date
,if(Key<>previous(Key),'Y','N') as Current?
RESIDENT MyTable
ORDER BY
Key
,Date DESC
;
STORE MyTable INTO MyTable.qvd (QVD);

Re: SQL queries in Qlikview

Anonymous — Tue, 12 Jul 2016 20:44:37 GMT

SQL query can only be used if you are pulling the data from a Database such as SQL or Oracle. Since it appears that you are trying to load fields from a simple Excel Sheet, you don't need to use SELECT here.

Try

TableName:

Load

Approver,

[Fiscal Contact],

[Fiscal Officer],

[Project Examiner]

From EXCEL FILE LOCATION PATH;

Re: SQL queries in Qlikview

Mon, 18 Jul 2016 18:58:01 GMT

To each and everyone of you guys, thanks alot for all your help. The dashboard I am creating is coming along very nicely thanks to you guys.