topic Re: SQL Queries with Qlikview in QlikView

SQL Queries with Qlikview

deepakqlikview_123 — Thu, 31 Jul 2014 12:47:59 GMT

Hi All,

Can you please give some examples or scenarios how can we use SQL queries in qlikview moreover please suggest how is it more important.

Thanks,

Deepak

Re: SQL Queries with Qlikview

agomes1971 — Thu, 31 Jul 2014 12:57:01 GMT

Hi,

you can use the same semantic as SQL Server.

First define your connection

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxxx;Data Source=xxxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=xxx;Use Encryption for Data=False;Tag with column collation when possible=False];

and then select the table or view:

Table:

SQL SELECT * from tttt;

Even you can use EXEC to execute store procedures.

Table:

SQL EXEC sp_xxxx;

I hope it helps

Re: SQL Queries with Qlikview

deepakqlikview_123 — Fri, 01 Aug 2014 04:32:40 GMT

Thanks Games,

Is there any use more than this can anyone can suggest.

Thanks,

Deepak

Re: SQL Queries with Qlikview

rahulhv1 — Fri, 01 Aug 2014 04:40:21 GMT

Deepak,

Do you want to extract data from where ? i mean from MS SQL / Oracel or from SAP form other data bases like Foxpro / Excel . Once you mention your specific requirment, one can give you examples.

Re: SQL Queries with Qlikview

deepakqlikview_123 — Fri, 01 Aug 2014 04:42:39 GMT

Hi Rahul,

I just want to info for extracting data from Oracle and sap.

Thanks,

Re: SQL Queries with Qlikview

amit_saini — Fri, 01 Aug 2014 04:45:05 GMT

Deepak,

Please see below:

First establish a connection like below and than write SQL query as below:

OLEDB CONNECT TO [Provider=...........=""] (XPassword is XXXXXXXXXXXXXXXXXXXXXXXXXXXXX);

SQL SELECT NLFDSTATIONNR,

SSTATIONBEZ

FROM QSYS.STATION;

Join

SQL SELECT

NLFDSTATIONNR ,

DTTSPROBE,

SZUSINFO1

FROM QSYS.STICHPROBE

where SZUSINFO1='590129204B232P' ;

Thanks,

Re: SQL Queries with Qlikview

tresB — Fri, 01 Aug 2014 04:52:32 GMT

When you fetch data from SQL supported database, you usually use SQL Select after establishing the db connectivity; you can't load it directly from db using Load like: Load field from <SQL/Oracle/...database>. Once the data is loaded using SQL Select you can use qlikview functions to transform the data on the loaded data (Preceding Load/Rsident Load) using Load statement. Now your question might be about usability the detailing SQL commands in qlikview. To answer that, let's have a scenario where you have two tables in the database with billions of data and you are actually bothered about few hundreads of data that is the resultant JOIN of these two tables. One way is to fetch all(billions) data from these tables into qlikview and then do the filter and join in qlikvew using load. Second way could be fetching the resulttant few hundreads of source data into qv and then operate on it. The first option is obviously not suggested. Hence, to go with better optimized option you have to apply the filter and join statement during the data load from source itself which is possible only using SQL Select statement. Therefore you write like:

Load

.....//Transformation code

; //Preceding load

SQL SELECT table_1.the_geom,table_1.iso_code,table_2.population FROM table_1, table_2 WHERE table_1.iso_code = table_2.iso

Re: SQL Queries with Qlikview

vardhancse — Fri, 01 Aug 2014 04:57:03 GMT

We can pull the data from SQL sever through ODBC connection.

Once the connection was established, we can start pulling the data.

We can give conditions based fields.what ever queries we are giving in SQL same can be implemened here.

more over there will be some functions in QV, we can use the same.

Re: SQL Queries with Qlikview

rustyfishbones — Wed, 22 Apr 2015 19:47:37 GMT

Hi tresesco,

you mentioned

SQL SELECT table_1.the_geom,table_1.iso_code,table_2.population

FROM table_1, table_2

WHERE table_1.iso_code = table_2.iso

What if I need to do something similar, but across 2 different Database.

I have a summarized Table from 1 Database that I extract from, with only 300,000+ records

I need to add Fields from another Database Table that contains 12 Million records, I only need the 300,000 that are associated through 3 common fields.

What's the best approach on this from a performance point of view

Regards

Alan