5 Replies Latest reply: Oct 5, 2017 5:32 AM by Peter Burnett RSS

    SQL DECLARE Statements in Qlik Sense LOAD script

    Peter Burnett

      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]