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,
