Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

akuttler
New Contributor III

Field not Found on Left join

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'

1 Solution

Accepted Solutions

Re: Field not Found on Left join

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';

2 Replies

Re: Field not Found on Left join

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';

dwforest
Valued Contributor

Re: Field not Found on Left join

Cant have . in alias