topic SQL query to QlikView with Joins - ODBC in QlikView

SQL query to QlikView with Joins - ODBC

Anonymous — Fri, 28 Sep 2018 16:13:27 GMT

Hello guys,

I'm having some problems with QlikView, I'm new at it, SQL SELECT it's working good but... i have a query in SQL that works perfectly but i need QlikView to execute it and I'm having no succes at it, the query in SQL is:

SELECT a.userid,

d.id as cursoid,

c.visible,

c.id as disciplinaid,

e.roleid

FROM

tb_log a INNER JOIN tb_user b ON a.userid=b.id

INNER JOIN tb_course c ON a.course=c.id

INNER JOIN tb_course_categories d ON c.category=d.id

INNER JOIN tb_role_assignments e ON a.userid=e.userid

INNER JOIN tb_context f ON e.contextid=f.id AND f.instanceid=c.id AND f.contextlevel=50

WHERE

b.firstname <> 'suporte'

GROUP BY

a.userid,

d.id,

c.id,

d.name,

c.shortname

ORDER BY

a.userid,

d.id,

c.id,

d.name,

c.shortname";

I think I should use the comand ALIAS a as tb_log; ... but still, i couldn't execute the query because of the Joins and I'm using ODBC conection.

Re: SQL query to QlikView with Joins - ODBC

vishsaggi — Fri, 28 Sep 2018 18:18:59 GMT

What is the error you are getting and in your SQL script why you using Group By clause when you do not have any aggregation functions used?

Re: SQL query to QlikView with Joins - ODBC

Anonymous — Fri, 28 Sep 2018 18:33:43 GMT

the script works,but i need QlikView to execute it, and then i will save the select result in a .qvd

so i need this SQL script in QlikView

Re: SQL query to QlikView with Joins - ODBC

vishsaggi — Fri, 28 Sep 2018 18:40:41 GMT

Yes, what is the error you are getting? Did you connect to your data base from QV using ODBC or OLEDB?

Use like

Connection String.

LOAD *;

SQL Select ....

FROM .....;

Re: SQL query to QlikView with Joins - ODBC

Anonymous — Fri, 28 Sep 2018 18:50:17 GMT

i'm connecting with

ODBC CONNECT TO...

Re: SQL query to QlikView with Joins - ODBC

Anonymous — Fri, 28 Sep 2018 18:55:09 GMT

I Tried

ODBC CONNECT TO ...;

LOAD*;

SQL SELECT a.userid,

d.id as cursoid,

c.visible,

c.id as disciplinaid,

e.roleid

FROM

tb_log a INNER JOIN tb_user b ON a.userid=b.id

INNER JOIN tb_course c ON a.course=c.id

INNER JOIN tb_course_categories d ON c.category=d.id

INNER JOIN tb_role_assignments e ON a.userid=e.userid

INNER JOIN tb_context f ON e.contextid=f.id AND f.instanceid=c.id AND f.contextlevel=50

WHERE

b.firstname <> 'suporte'

it works THANKS A LOT i'm reeeeaally new at QlikView, so now I need to save the result of this select using

STORE ??? INTO Query1.qvd (qvd);

Re: SQL query to QlikView with Joins - ODBC

vishsaggi — Fri, 28 Sep 2018 19:00:48 GMT

Ok good. Give a path when you store into like

Just above the Load *; give a table name like

TableNamehere:

LOAD *;

SQL Select.....;

Store TableNamehere INTO C:\Folder1\QV\TableNamehere.qvd(qvd);

Re: SQL query to QlikView with Joins - ODBC

Anonymous — Fri, 28 Sep 2018 19:06:34 GMT

is there a way that I can store the result of the select in a variable ? like an to use WHILE comand and chek one by one ?

Re: SQL query to QlikView with Joins - ODBC

Anonymous — Fri, 28 Sep 2018 19:59:58 GMT

Hi Leonardo,

Check the following points

If there is any syntax error as I have observed a unnecessary " (double quote)
Group By without an Aggregation function

Please share log file and data source if possible.

Regards

Chandan

Re: SQL query to QlikView with Joins - ODBC

vishsaggi — Fri, 28 Sep 2018 20:33:54 GMT

Not sure what you mean why you want to store this in a variable? If you can tell me what your analysis is we can suggest accordingly..

Re: SQL query to QlikView with Joins - ODBC

Anonymous — Mon, 01 Oct 2018 12:27:42 GMT

ok its quite long...

today we`re using a PHP script to do that, first connect to db then execute that query and the result is saved in a variable $students, then we use the comand while and another query like that:

please look at the attachment there is the code in PHP that i need Qlik to execute and generate a QVD in the end with the table "tb_temp_temptable"

Re: SQL query to QlikView with Joins - ODBC

arvind1494 — Mon, 01 Oct 2018 12:29:08 GMT

Yes