Translating SQL query in Qlikview

[tka_ovako]

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)

[#2]

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

[rbecher]

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

[santiago_respane · Accepted solution]

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,

[jonathandienst]

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.

[tka_ovako]

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.

[tka_ovako]

Thanks for all the fast responses!!

[santiago_respane]

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,

[tka_ovako]

Santiago or other pro.

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