2 Replies Latest reply: Jun 5, 2018 3:58 PM by David Forest RSS

    Field not Found on Left join

    Ashley Kuttler

      Hello,

       

      Can someone help me figure out why I am getting an error "Field 'CPT.Cpt' not found" in my data load?

       

      LOAD

       

          Date(Floor(ServiceDate)) as "Date",

          PatientId as ENC.PATIENTID,

          CPT.Cpt as ENC.CPT,

          DOC.UserName as RenderingPhysician,

          Account as ENC.ITEMTYPE,

          Unit as ENC.UNITS,

          PRAC.PracticeName,

          EncounterId,

          CPT.CptCategory as ENC.CPTCATEGORY,

          PatientId & [DOC.UserName] & ServiceDate as Visits;

      SQL SELECT *

      FROM [DataMart.dbo.patientaccountreport PA

      LEFT JOIN [DataMart].[dbo].[patientaccountcpt] CPT

      ON CPT.EncounterCodeId = PA.EncounterCodeId

      LEFT JOIN [DataMart].[dbo].[patientaccountuser] DOC

      ON DOC.UserId = PA.RenderingId

      LEFT JOIN [DataMart].[dbo].[patientaccountpractice] PRAC

      ON PRAC.PracticeId = PA.PracticeId

      WHERE [TransactionDate] <= '04-30-2018'

        • Re: Field not Found on Left join
          Nicole Smith

          You should put your fields directly in the SELECT (the LOAD is unnecessary (and so is the SQL if all you are doing is a SELECT)

           

          Example:

          TableName:

          SELECT t1.Field1,

               t2.Field2,

               t3.Field3

          FROM Table1 t1

          LEFT JOIN Table2 t2

               ON t1.t2id = t2.t2id

          LEFT JOIN Table3 t3

               ON t2.t3id = t3.t3id;

           

          So yours would look something like (I made some edits to your field load based on assumptions on what might be in those, so you may need to cast/write them differently than in Qlik):

          TableName:

          SELECT

              CAST(ServiceDate AS DATE) as "Date",

              PatientId as ENC.PATIENTID,

              CPT.Cpt as ENC.CPT,

              DOC.UserName as RenderingPhysician,

              Account as ENC.ITEMTYPE,

              Unit as ENC.UNITS,

              PRAC.PracticeName,

              EncounterId,

              CPT.CptCategory as ENC.CPTCATEGORY,

              CAST(PatientId AS VARCHAR) + DOC.UserName + ServiceDate as Visits

          FROM [DataMart.dbo.patientaccountreport PA

          LEFT JOIN [DataMart].[dbo].[patientaccountcpt] CPT

          ON CPT.EncounterCodeId = PA.EncounterCodeId

          LEFT JOIN [DataMart].[dbo].[patientaccountuser] DOC

          ON DOC.UserId = PA.RenderingId

          LEFT JOIN [DataMart].[dbo].[patientaccountpractice] PRAC

          ON PRAC.PracticeId = PA.PracticeId

          WHERE [TransactionDate] <= '04-30-2018';

          • Re: Field not Found on Left join
            David Forest

            Cant have . in alias