Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jaswanthchitra9
Contributor II
Contributor II

SQL to Qliksense

Hi All,

I want convert the Sql Script to Qliksense Application. please find the below script for your reference. Can any one please help me out on this.

select
incidentid,
[DateTimeCreatedOn],
[Stat_DateTimeResolved],
I.priority,
540 + datediff(minute,[DateTimeCreatedOn],DATEADD(hour, 17, DATEDIFF(d, 0, [DateTimeCreatedOn]))) as date_diff_in_mintues,
smr.dbo.NetBusinessDaysHol([DateTimeCreatedOn], [Stat_DateTimeResolved], '8:00', '17:00', 0, 0, 0, '')/60,
CASE
WHEN I.priority =2 Then
Case
when cast([DateTimeCreatedOn] as date) = cast([Stat_DateTimeResolved] as date)
THEN 1
when datepart(hour,[DateTimeCreatedOn]) in(15,16) Then
Case when smr.dbo.NetBusinessDaysHol([DateTimeCreatedOn], [Stat_DateTimeResolved], '8:00', '17:00', 0, 0, 0, '')/60
<=( 540 + datediff(minute,[DateTimeCreatedOn],DATEADD(hour, 17, DATEDIFF(d, 0, [DateTimeCreatedOn])) ) )
THEN 1
Else 0 End
when datepart(hour,[DateTimeCreatedOn]) >=17 Then
case when smr.dbo.NetBusinessDaysHol([DateTimeCreatedOn], [Stat_DateTimeResolved], '8:00', '17:00', 0, 0, 0, '')/60 <=540 THEN 1
ELSE 0 END
Else 0 End
END AS Resolve_SLA_Met
From Cherwell.[dbo].[Incident_Final_Hist] I With (Nolock) where incidentid in ('2412763','2412550','2366140','1974128','1725767','1925192')

 

Thanks in Advance!

Labels (4)
3 Replies
Aamer_17
Partner - Contributor II
Partner - Contributor II

Hi Jaswanth,

First, create new connection for SQL DB from which you need tables if not created.

then add the connection statement and write your sql query , 

Example: 

LIB CONNECT TO 'Your SQL DB connection name';

IncidentTable:

SQL select
incidentid,
[DateTimeCreatedOn],
[Stat_DateTimeResolved],
I.priority,
540 + datediff(minute,[DateTimeCreatedOn],DATEADD(hour, 17, DATEDIFF(d, 0, [DateTimeCreatedOn]))) as date_diff_in_mintues,
smr.dbo.NetBusinessDaysHol([DateTimeCreatedOn], [Stat_DateTimeResolved], '8:00', '17:00', 0, 0, 0, '')/60,
CASE
WHEN I.priority =2 Then
Case
when cast([DateTimeCreatedOn] as date) = cast([Stat_DateTimeResolved] as date)
THEN 1
when datepart(hour,[DateTimeCreatedOn]) in(15,16) Then
Case when smr.dbo.NetBusinessDaysHol([DateTimeCreatedOn], [Stat_DateTimeResolved], '8:00', '17:00', 0, 0, 0, '')/60
<=( 540 + datediff(minute,[DateTimeCreatedOn],DATEADD(hour, 17, DATEDIFF(d, 0, [DateTimeCreatedOn])) ) )
THEN 1
Else 0 End
when datepart(hour,[DateTimeCreatedOn]) >=17 Then
case when smr.dbo.NetBusinessDaysHol([DateTimeCreatedOn], [Stat_DateTimeResolved], '8:00', '17:00', 0, 0, 0, '')/60 <=540 THEN 1
ELSE 0 END
Else 0 End
END AS Resolve_SLA_Met
From Cherwell.[dbo].[Incident_Final_Hist] I With (Nolock) where incidentid in ('2412763','2412550','2366140','1974128','1725767','1925192');

Store [IncidentTable] into [your qvd storage path/QVD_Name.qvd](qvd);

Drop table IncidentTable;

 

further store your SQL Logic data into QVD & use the QVD file for your Application.

Hope this is help full for you.

 

Thanks, 

jaswanthchitra9
Contributor II
Contributor II
Author

Thanks for the reply!

Aamer_17
Partner - Contributor II
Partner - Contributor II

Most Welcome:)