Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

SQL DECLARE Statements in Qlik Sense LOAD script

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]

1 Solution

Accepted Solutions
MVP
MVP

Re: SQL DECLARE Statements in Qlik Sense LOAD script

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

;

5 Replies
MVP
MVP

Re: SQL DECLARE Statements in Qlik Sense LOAD script

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

;

Not applicable

Re: SQL DECLARE Statements in Qlik Sense LOAD script

That's fantastic! Thanks!

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

MVP
MVP

Re: SQL DECLARE Statements in Qlik Sense LOAD script

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

MVP
MVP

Re: SQL DECLARE Statements in Qlik Sense LOAD script

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

Not applicable

Re: SQL DECLARE Statements in Qlik Sense LOAD script

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

Community Browser