topic Making this SQL code Compatible with QlikView in QlikView

Making this SQL code Compatible with QlikView

Wed, 25 Nov 2020 16:16:04 GMT

How do I make this compatible with QlikView?

USE MPL

SELECT ITMSNumber,Date_Chg,DATEDIFF(dd,Date_Chg,GETDATE()) AS tDays

FROM tblSTRATEGY Strgy

inner join

( SELECT ChangeKeyValue AS ITMS_Chg,MAX(ChangeDate) AS Date_Chg

FROM tblCHANGE Chg

WHERE ChangeFieldName='DATALOADSTATUSID'

AND ChangeNewValue='Quarantined - DSC'

GROUP BY ChangeKeyValue

) Chg1 ON Chg1.ITMS_Chg=Strgy.ITMSNumber

WHERE Strgy.DATALOADSTATUSID=30

ORDER BY TDAYS

I need to be able to load ITMSNumber and tDays to make this work right-

Right now these numbers above should add up to 41.

Re: Making this SQL code Compatible with QlikView

jonathandienst — Wed, 25 Jan 2017 12:13:28 GMT

How is that code incompatible? Drop the USE statement and append SQL in front. The statement is passed to the SQL server and executed by that server, not by QV. QV can then consume the data.

Your OLEDB or ODBC connection will point you to the MPL database/catalog, that's why would not normally need the USE statement.

CONNECT .....

[Data]:

SQL

SELECT ITMSNumber,Date_Chg,DATEDIFF(dd,Date_Chg,GETDATE()) AS tDays

FROM tblSTRATEGY Strgy

inner join

( SELECT ChangeKeyValue AS ITMS_Chg,MAX(ChangeDate) AS Date_Chg

FROM tblCHANGE Chg

WHERE ChangeFieldName='DATALOADSTATUSID'

AND ChangeNewValue='Quarantined - DSC'

GROUP BY ChangeKeyValue

) Chg1 ON Chg1.ITMS_Chg=Strgy.ITMSNumber

WHERE Strgy.DATALOADSTATUSID=30

ORDER BY TDAYS;

...

Re: Making this SQL code Compatible with QlikView

Wed, 25 Jan 2017 12:53:28 GMT

Thank you!

Re: Making this SQL code Compatible with QlikView

Wed, 25 Jan 2017 12:57:49 GMT

It still doesn't add up to 41 though. With this code it only adds up to 38...

Re: Making this SQL code Compatible with QlikView

Wed, 25 Jan 2017 13:08:25 GMT

[Data]:

SQL

SELECT ITMSNumber,Date_Chg,DATEDIFF(dd,Date_Chg,GETDATE()) AS tDays

FROM tblSTRATEGY Strgy

inner join

( SELECT ChangeKeyValue AS ITMS_Chg,MAX(ChangeDate) AS Date_Chg

FROM tblCHANGE Chg

WHERE ChangeFieldName='DATALOADSTATUSID'

AND ChangeNewValue='Quarantined - DSC'

GROUP BY ChangeKeyValue

) Chg1 ON Chg1.ITMS_Chg=Strgy.ITMSNumber

WHERE Strgy.DATALOADSTATUSID=30

ORDER BY TDAYS;