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

[#2]

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

[johnw]

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.

[#4]

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,

T

[vishsaggi]

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.

[vishsaggi]

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.

[johnw]

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);

[Anonymous]

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;

[#9]

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.