SQL queries in Qlikview

[#1]

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

[vishsaggi]

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

[johnw]

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.

[#4]

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,

T

[#5]

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:

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,

T

[vishsaggi]

Hello Tej,

Use

TableName:

Load *;

SQL SELECT 'Pat_ID',

Price

FROM ....... ;

Try this.

[johnw]

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.

[MarcoWedel]

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

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

regards

Marco

[vishsaggi]

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.

[Peter_Cammaert]

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