Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
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';
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';
Cant have . in alias