Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
There’s a lot of static to be read on the forums for those times you need to send a SQL query with semicolons from Qlik.
Here’s a way that should work as we don't always have the ability to save stored procedures.
Create a SQL variable and send that via a Qlik variable.
Example: Qlik Load
//Qlik Variable (Change ‘Set vSql’ to ‘Let vSql’ when needed)
Set vSql ="
DECLARE @SQL VARCHAR(8000)
SET @SQL =
'SET NOCOUNT ON;
declare @starttime as datetime2;
declare @endtime as datetime2;
set @starttime = getdate();
IF OBJECT_ID(' + CHAR(39) + 'tempdb..#tempresults' + CHAR(39) + ') IS NOT NULL DROP TABLE #tempresults;
with cte as (
select AdmitDateTime as LastPatientRegActivity,
RegistrationType,
ROW_NUMBER() OVER (ORDER BY AdmitDateTime desc) as num
from [dbo].[YourTable]
where RegistrationType in (' + CHAR(39) + 'IN' + CHAR(39) + ',' + CHAR(39) + 'INO' + CHAR(39) + ',' + CHAR(39) + 'ER' + CHAR(39) + ')
)
select
datediff(mi,cte.LastPatientRegActivity,getdate()) as MinutesSinceLastPatientActivity,
cte.LastPatientRegActivity as LastPatientRegActivityDateTime
into #tempresults
from cte
where num = 1;
set @endtime = getdate();
select *, datediff(ms,@starttime,@endtime) as LoadTimeMS
from #tempresults;
IF OBJECT_ID(' + CHAR(39) + 'tempdb..#tempresults' + CHAR(39) + ') IS NOT NULL DROP TABLE #tempresults;'
EXEC(@SQL);
";
Sample Load
Temp1:
Load *;
SQL
$(vSql);