Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
tka_ovako
Contributor II
Contributor II

Translating SQL query in Qlikview

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)

1 Solution

Accepted Solutions
santiago_respane
Specialist
Specialist

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,

View solution in original post

8 Replies
Not applicable

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
MVP
MVP

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

Vizlib Head of R&D
santiago_respane
Specialist
Specialist

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,

View solution in original post

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
tka_ovako
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

Thanks for all the fast responses!!

santiago_respane
Specialist
Specialist

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
Contributor II
Contributor II
Author

Santiago or other pro.

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