Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tiklam_dynasys
Contributor

SubQuery In Qlik Sense

Hi all,

In QlikView, I can do

SQL select
CUSTNA+' - ' + CONTNO as 'Cusomter name and Contract No                                                                       ',
CONTNO as 'Contract no',
CONTSQ as 'Contract No. Sequance',
CONTTP as 'Contract Type',
CUSTNO as 'Customer no.',
CUSTNA as 'Customer name',
QUOT_A as 'ABAP Quota',
QUOT_F as 'Functional Quota',
QUOT_B as 'Basis Quota',
QUOT_P as 'Project Quota',
QUOT_O as 'Others Quota',
QUOT_M as 'Monthly Call Quota',
convert(datetime, VALIDF, 112) as 'Valid Date From',
convert(datetime,VALIDT, 112) as 'Valid Date To',
CREABY as 'Create By',
CREATD as 'Create Date',
CREATT as 'Create Time',
MODBY as 'Modify By',
MODDTE as 'Modify Date',
MODDTT as 'Modify Time',
PROJID as 'Project ID',
PROJDES as 'Project Description',
STATUS as 'Contract Status',
CONTDES as 'Contract Description'
from sol.ZCONTRACT a
where a.CONTSQ = (select max(CONTSQ) from sol.ZCONTRACT where CONTNO = a.CONTNO)
and a.CONTTP <> 'CLOSED'
and a.CONTTP like 'SUPPORT%'
and a.CONTNO not in ('A1-SUPPORT','DYNSTUDY','DYNAPROJ','TEMPPROJ')
and a.CONTNO not like 'VSAP_%'
order by a.CUSTNA, a.CONTNO;



Can we do the same in QlikSense by using subquery?


Best Regards,

Louis

5 Replies
undergrinder
Valued Contributor II

Re: SubQuery In Qlik Sense

Hi Ka,

I think you can do the same, because Qlik send the SQL statements to database, instead of execute ownself.

You can test it with Qlik Sense dekstop, it is free, and you can check, if it fits your requirement.

G.

tiklam_dynasys
Contributor

Re: SubQuery In Qlik Sense

Thank you for reply.

I have tried but error is prompted.

And I find that QlikSense cannot run the syntax as below

select CustomerID, CompanyName
from customers as a
where exists
(
  
select * from orders as b
  
where a.CustomerID = b.CustomerID
  
and ShipCountry = 'UK'
);



undergrinder
Valued Contributor II

Re: SubQuery In Qlik Sense

According to help it is possible,

SQL ‒ Qlik Sense

The SQL statement allows you to send an arbitrary SQL command through an ODBC or OLE DBconnection.

Have you tried the query at management studio of db?

I think the subquery scope is out of a then where a.CustomerID = b.CustomerID is not valid statement.

G.

tiklam_dynasys
Contributor

Re: SubQuery In Qlik Sense

Error is prompted when i run the code as below:

[ZCONTRACT]:

LOAD CONTNO,

  CONTSQ;

SQL SELECT CONTNO, 

  CONTSQ

  

FROM ZCONTRACT as a where a.CONTSQ = 1;


Untitled 2.png

Do you know why??

undergrinder
Valued Contributor II

Re: SubQuery In Qlik Sense

The syntax seems right.

I suggest you try your sql script at management studio first at db.

For example:

  • MS/SQL: Microsoft SQL Server Management Studio
  • PostgreSQL: PgAdmin
  • Oracle: Sql developer or PL/SQL developer

If your script works well then transport to Qlik Sense or define stored procedure to do the job.

G.