SubQuery In Qlik Sense

[Anonymous]

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

[undergrinder]

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.

[Anonymous]

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]

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.

[Anonymous]

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;

Do you know why??

[undergrinder]

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.