topic Re: Translating SQL query in Qlikview in QlikView

Translating SQL query in Qlikview

tka_ovako — Wed, 25 Nov 2020 16:16:04 GMT

Hello.

I am newbie to use Qlikview and I need to know is there any way to get attached query to work in Qlikview?

DECLARE @nrp_starttime as datetime

DECLARE @nrp_endtime as datetime

DECLARE @nrp_interval as int

DECLARE @nrp_taglist as varchar (4000)

SET @nrp_starttime = '02.04.2016 7:00:00'

SET @nrp_endtime = '03.04.2016 10:00:00'

SET @nrp_interval = 5

SET @nrp_taglist = 'HISTORIAN01/SCADA1.XX.OXX1.OTXXX00S/INTERPOLATED'

SELECT StartTime,EndTime,Value1,Status1 from NovotekReportPlusConfiguration.dbo.nrp_HistorianTableFunctionIntervalString(@nrp_starttime,@nrp_endtime,@nrp_interval,@nrp_taglist)

Re: Translating SQL query in Qlikview

Wed, 18 May 2016 12:29:04 GMT

Timo, as per your select statement, the NovotekReportPlusConfiguration.dbo.nrp_HistorianTableFunctionIntervalString is a function with 4 input parameters.

You can implement the function logic in Qlikview. So please go through the function code and try to implement what function is doing. or post the function script

Re: Translating SQL query in Qlikview

rbecher — Wed, 18 May 2016 12:40:34 GMT

Hi Timo,

you can define same variables in QlikView script and then probably pass as literal string to the SQL function:

SET nrp_starttime = 02.04.2016 7:00:00;

..etc.

SELECT StartTime,EndTime,Value1,Status1 from NovotekReportPlusConfiguration.dbo.nrp_HistorianTableFunctionIntervalString('$(nrp_starttime)','$(nrp_endtime)','$(nrp_interval)','$(nrp_taglist)');

- Ralf

Re: Translating SQL query in Qlikview

santiago_respane — Wed, 18 May 2016 13:49:48 GMT

Hi Timo,

You can use your query directly from QV using SQL SELECT... having your values in variables and passing them to the function kind of like this:

SET v.nrp_starttime = '02.04.2016 7:00:00';

SET v.nrp_endtime = '03.04.2016 10:00:00';

SET v.nrp_interval = 5;

SET v.nrp_taglist = 'HISTORIAN01/SCADA1.XX.OXX1.OTXXX00S/INTERPOLATED';

TABLE1:

SQL SELECT StartTime,

EndTime,

Value1,Status1

from NovotekReportPlusConfiguration.dbo.nrp_HistorianTableFunctionIntervalString('$(v.nrp_starttime)','$(v.nrp_endtime)',$(v.nrp_interval),'$(v.nrp_taglist)');

Doing this you wont have to replicate your logic in QV.

Let me know if this helps.

Kind regards,

Re: Translating SQL query in Qlikview

jonathandienst — Wed, 18 May 2016 14:00:52 GMT

As lomg as there is no semi-colon in the SQL expression, you can put the expression without changes into a SQL statement:

SQL

DECLARE @nrp_starttime as datetime

DECLARE @nrp_endtime as datetime

DECLARE @nrp_interval as int

DECLARE @nrp_taglist as varchar (4000)

SET @nrp_starttime = '02.04.2016 7:00:00'

SET @nrp_endtime = '03.04.2016 10:00:00'

SET @nrp_interval = 5

SET @nrp_taglist = 'HISTORIAN01/SCADA1.XX.OXX1.OTXXX00S/INTERPOLATED'

SELECT StartTime,EndTime,Value1,Status1 from NovotekReportPlusConfiguration.dbo.nrp_HistorianTableFunctionIntervalString(@nrp_starttime,@nrp_endtime,@nrp_interval,@nrp_taglist);

Everything between the SQL and the ; is passed to the SQL server and the dataset from the SELECT is passed back. Of course the previous posts are more compact, but I am just using this to illustrate a point.

Re: Translating SQL query in Qlikview

tka_ovako — Fri, 20 May 2016 11:24:15 GMT

Thank you Santiago!

That works fine. I've got a lot of work done with that query. I also have more to ask. What should I do when I need to get information from more than one tag?

SET v.nrp_starttime = '02.04.2016 7:00:00';

SET v.nrp_endtime = '03.04.2016 10:00:00';

SET v.nrp_interval = 5;

SET v.nrp_taglist = 'HISTORIAN01/SCADA1.XX.OXX1.OTXXX00S/INTERPOLATED'

+ '|HISTORIAN01/SCADA1.XX.ABJJ2/INTERPOLATED';

etc.

Re: Translating SQL query in Qlikview

tka_ovako — Fri, 20 May 2016 11:25:14 GMT

Thanks for all the fast responses!!

Re: Translating SQL query in Qlikview

santiago_respane — Fri, 20 May 2016 13:10:00 GMT

Hi Timo,

if your issue got solved, please don't forget to mark the answer that helped you as correct in order to close the thread.

Qlik Community Tip: Marking Replies as Correct or Helpful

Kind regards,

Re: Translating SQL query in Qlikview

tka_ovako — Wed, 25 May 2016 07:31:51 GMT

Santiago or other pro.

Does anyone have any suggestion how I call 2 or more tags?