topic Re: SQL to Qliksense in App Development

SQL to Qliksense

jaswanthchitra9 — Wed, 11 Jan 2023 12:01:15 GMT

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!

Re: SQL to Qliksense

Aamer_17 — Mon, 16 Jan 2023 06:40:37 GMT

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,

Re: SQL to Qliksense

jaswanthchitra9 — Mon, 16 Jan 2023 06:44:35 GMT

Thanks for the reply!

Re: SQL to Qliksense

Aamer_17 — Wed, 25 Jan 2023 08:52:59 GMT

Most Welcome:)