topic Re: SQL DECLARE Statements in Qlik Sense LOAD script in App Development

SQL DECLARE Statements in Qlik Sense LOAD script

Tue, 03 Oct 2017 14:19:52 GMT

Hi,

I'm having trouble getting my head around how to integrate DECLARE statements (in the SQL sense of setting variables, temporary tables etc) into my Qlik Sense Load script.

The script below gives me an error message which I believe is caused by my DECLARE parameters. I'm also aware it will probably involve using Qlik's 'LET' function.

Any suggestions welcome. Script below:

DECLARE @StartDate DATE, @EndDate DATE

SET @StartDate = GETDATE()-183

SET @EndDate = GETDATE()

DECLARE @IPAudit Table

(SourceValue nvarchar(36),

MainSourceValue nvarchar(38),

TransCom nvarchar (200),

CreateDate DATETIME,

SystemUser nvarchar (100),

PatientID nvarchar(25),

TerminalLoc nvarchar (200))

INSERT INTO @IPAudit

select SUBSTRING(FAF.SOURCE_VALUE,2,36),

faf.SOURCE_VALUE,

datc.TRANSACTION_COMMAND,

faf.CREATE_DTTM,

dsu.USERS_NAME,

faf.DIM_PATIENT_ID,

st.USERGROUP_1

from FACT_AUDIT_FULL faf with (nolock)

inner join DIM_AUDIT_TRANSACTION_COMMAND as datc with (nolock)

on faf.DIM_AUDIT_TRANSACTION_COMMAND_ID = datc.DIM_AUDIT_TRANSACTION_COMMAND_ID

inner Join DIM_SYSTEM_TERMINAL as st with (nolock)

on faf.DIM_SYSTEM_TERMINAL_ID = st.DIM_SYSTEM_TERM_ID

inner join DIM_SYSTEM_USER as dsu with (nolock)

on faf.DIM_SYSTEM_USER_ID = dsu.DIM_SYSTEM_USER_ID

where faf.LOG_DTTM between @StartDate and @EndDate

and datc.DIM_AUDIT_TRANSACTION_COMMAND_ID in('21481','21851')

SELECT dpat.PAS_ID as'Adm Tim.K number',

ssu.SSU_MAIN_CODE as 'Adm Tim.Admitting Ward Code',

ssu.SSU_NAME as 'Adm Tim.Admitting Ward',

fis.ADMIT_DTTM as 'Adm Tim.Date/time Admission',

ipa.CreateDate as 'Adm Tim.Date/time Admission logged',

(DATEDIFF(SECOND, fis.ADMIT_DTTM, ipa.CreateDate)/3600.0) as 'Adm Tim.Timeliness (hrs)',

ipa.TransCom as 'Adm Tim.Medway action',

ipa.SystemUser as 'Adm Tim.Admission end User',

ipa.TerminalLoc as 'Adm Tim.Terminal Location'

FROM FACT_IP_SPELL fis WITH(NOLOCK)

inner join @IPAudit IPA on fis.IP_SPELL_EXTERNAL_ID = IPA.SourceValue

inner join dim_patient dpat with (nolock) on fis.DIM_PATIENT_ID = dpat.DIM_PATIENT_ID

inner join DIM_LOOKUP_ADMIMETH dlam with (nolock) on fis.DIM_ADMET_ID = dlam.DIM_LOOKUP_ADMIMETH_ID

inner join DIM_SITE_SERVICE_UNIT SSU with (nolock) on fis.DIM_ADM_WARD_ID = ssu.DIM_SSU_ID

inner join DIM_SITE_SERVICE_UNIT SSU2 with (nolock)on fis.DIM_DIS_WARD_ID = SSU2.DIM_SSU_ID

inner Join DIM_HC_PROFESSIONAL as HP with (nolock) on fis.DIM_ADM_HCP_ID = HP.DIM_HCP_ID

inner Join DIM_HC_PROFESSIONAL as HP2 with (nolock) on fis.DIM_DIS_HCP_ID = HP2.DIM_HCP_ID

inner Join DIM_LOOKUP_DISCH_DEST as DDD with (nolock)on fis.DIM_DISDE_ID = DDD.DIM_LOOKUP_DISCH_DEST_ID

inner Join DIM_LOOKUP_ADMIMETH as DMA with (nolock) on fis.DIM_ADMET_ID = DMA.DIM_LOOKUP_ADMIMETH_ID

inner Join DIM_LOOKUP_DISMETH as DMD with (nolock) on fis.DIM_DISME_ID = DMD.DIM_LOOKUP_DISMETH_ID

inner Join DIM_LOOKUP_ADMISORC as LAS with (nolock) on fis.DIM_ADSOR_ID = LAS.DIM_LOOKUP_ADMISORC_ID

inner Join DIM_SPECIALTY as DS with (nolock) on fis.DIM_ADM_SPECT_ID = DS.DIM_SPECIALTY_ID

inner Join DIM_LOOKUP_INTMANIG as DINTMAN with (nolock) on Fis.DIM_INMGT_ID = DINTMAN.DIM_LOOKUP_INTMANIG_ID

WHERE fis.ADMIT_DTTM between @StartDate and @EndDate

ORDER BY [Adm Tim.Admitting Ward]

Re: SQL DECLARE Statements in Qlik Sense LOAD script

petter — Wed, 04 Oct 2017 07:37:11 GMT

I think you need to have the T-SQL statement SET NOCOUNT ON at the top of your SQL script to get it working inside a Load Script:

This simplified script worked well for me in Qlik Sense:

LIB CONNECT TO 'MS SQL';

[SQLDATA]:
SQL
    SET NOCOUNT ON

    DECLARE @StartDate DATE, @EndDate DATE

    SET @StartDate = GETDATE()-183
    SET @EndDate   = GETDATE()

    DECLARE @IPAudit Table
    (SourceValue nvarchar(36),
    MainSourceValue nvarchar(38),
    TransCom nvarchar (200),
    CreateDate DATETIME,
    SystemUser nvarchar (100),
    PatientID nvarchar(25),
    TerminalLoc nvarchar (200))

    INSERT INTO @IPAudit
    SELECT
        'SourceValue','MainSourceValue','Transcom','2017-10-04','SystemUser','PatientID','TerminalLoc'

    SELECT * FROM @IPAudit
;

Re: SQL DECLARE Statements in Qlik Sense LOAD script

Wed, 04 Oct 2017 12:42:36 GMT

That's fantastic! Thanks!

Slightly different question: why does 'SET NOCOUNT ON' work?

Re: SQL DECLARE Statements in Qlik Sense LOAD script

petter — Wed, 04 Oct 2017 16:38:25 GMT

Normally a T-SQL query will report the number of rows returned for each statement and doing SET NOCOUNT ON will prevent that. The "remarks" in Microsoft's documentation will give you some further explanation:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql

Re: SQL DECLARE Statements in Qlik Sense LOAD script

petter — Thu, 05 Oct 2017 07:32:12 GMT

If you think your question has been answered please mark the response that answered your question as answered to close the thread. If not please elaborate what you think didn't get answered...

Re: SQL DECLARE Statements in Qlik Sense LOAD script

Thu, 05 Oct 2017 09:32:09 GMT

‌Apologies. Couldn't figure out how to mark as answered!