Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ashishpalkar
Creator III
Creator III

How to us SQL | WITH clause in Qlik

Hi There

I am executing below query in SQL , this query has SQL | WITH clause. Is there any better way to execute the same query in Qlik?

WITH PRICES AS

(

SELECT

DISTINCT P.INSTRUMENTID

,CONVERT(DATETIME, CAST(P.TIMEID AS CHAR(8)),0) AS VALUATIONDATE

,P.NETQUANTITY

,IH.LASTPRICE

,IH.PRICEDATE

,P.LOCALCURRENCY

FROM POSITION P

JOIN ACCOUNT A ON A.ACCOUNTID=P.ACCOUNTID

LEFT OUTER JOIN INSTRUMENTHISTORY IH ON IH.INSTRUMENTID=P.INSTRUMENTID AND P.TIMEID=IH.TIMEID

WHERE

p.TimeId between CAST(CONVERT(varchar(20),getdate()-90,112) AS INT) and CAST(CONVERT(varchar(20),getdate(),112) AS INT)

--AND A.ACCOUNTID='ATRIUMIV'

AND P.NETQUANTITY<>0

AND P.INSTRUMENTID<>235

)

SELECT

distinct P.INSTRUMENTID

,ISNULL(ie.INSTRUMENTCUSIP,ISNULL(ie.INSTRUMENTSEDOL,ISNULL(ie.INSTRUMENTISIN,ie.INSTRUMENTTICKER))) AS INVESTMENTID

,IE.NAME AS INVESTMENTNAME

,VALUATIONDATE

,NETQUANTITY

,P.LASTPRICE

,P.PRICEDATE

,LOCALCURRENCY

FROM PRICES P

JOIN INSTRUMENTEQUITY IE ON IE.INSTRUMENTID=P.INSTRUMENTID

Thanks in advance,

2 Replies
arulsettu
Master III
Master III

just add SQL before

SQL

WITH PRICES AS

(

SELECT

DISTINCT P.INSTRUMENTID

,CONVERT(DATETIME, CAST(P.TIMEID AS CHAR(8)),0) AS VALUATIONDATE

,P.NETQUANTITY

,IH.LASTPRICE

,IH.PRICEDATE

,P.LOCALCURRENCY

FROM POSITION P

JOIN ACCOUNT A ON A.ACCOUNTID=P.ACCOUNTID

LEFT OUTER JOIN INSTRUMENTHISTORY IH ON IH.INSTRUMENTID=P.INSTRUMENTID AND P.TIMEID=IH.TIMEID

WHERE

p.TimeId between CAST(CONVERT(varchar(20),getdate()-90,112) AS INT) and CAST(CONVERT(varchar(20),getdate(),112) AS INT)

--AND A.ACCOUNTID='ATRIUMIV'

AND P.NETQUANTITY<>0

AND P.INSTRUMENTID<>235

)

SELECT

distinct P.INSTRUMENTID

,ISNULL(ie.INSTRUMENTCUSIP,ISNULL(ie.INSTRUMENTSEDOL,ISNULL(ie.INSTRUMENTISIN,ie.INSTRUMENTTICKER))) AS INVESTMENTID

,IE.NAME AS INVESTMENTNAME

,VALUATIONDATE

,NETQUANTITY

,P.LASTPRICE

,P.PRICEDATE

,LOCALCURRENCY

FROM PRICES P

JOIN INSTRUMENTEQUITY IE ON IE.INSTRUMENTID=P.INSTRUMENTID

paulosilva
Contributor II
Contributor II

I do this but my result not load any line and the same query in database have a result with many lines.

 

Can help me with this ?


LOAD * ;

contas_receber:
SQL
WITH contas_receber AS ....