Field Name 'Not found'?

[teetoes]

I have been working with this query and cannot get it to recognize the column names. I keep getting the error Field 'cc.Legacy_Subline_Code' not found. I cannot think of anything else to try. Here's what I've done so far:

1. Moved the case statements to the Load  statement and updated the syntax

2. Copy pasted the name exactly from the load statement into the query to ensure all capitalization and spacing is correct )I tried with the cc. and without)

3. Pasted the entire query into a new app and tried loading

I'm out of ideas. Can anyone help? I've removed the From, Where, Group By and Order by details, but it's all there and Group By and Order By are just a copy paste of one another. I should also mention that I've run my query in SSMS and had no issues until now when trying to automate it for a user.

Load
If(cc.Legacy_Subline_Code = '240','Other Health Care Professional Liability',
if( cc.Legacy_Subline_Code = '210', 'Hospital Professional Liability',
if( cc.Legacy_Subline_Code = '230', 'Physicians, Surgeons, and Dentists Professional Liability',
if( cc.Legacy_Subline_Code = '220', 'Other Health Care Facilities Liability',
if( cc.Legacy_Subline_Code = '317', 'Professional Liability - Other than Hospital, Health Care, Physicians, Surgeons and Dentists and Management Protection Program'
,cc.Legacy_Subline_Code))))) as Subline,

External_Reference_Text,
GWP,
Earned;

SQL
SELECT
cc.Legacy_Subline_Code,
lioc.External_Reference_Text,
SUM(fpp.Written_Premium_Amount) as GWP,
SUM(Earned_Premium_Amount) as Earned

FROM ...

[rwunderlich · Accepted solution]

Some drivers will fold the unquoted SQL column names to upper case. Try referring to the field as CC.LEGACY_SUBLINE_CODE in the Load statement. 

Another way to diagnose this is to just run the SQL SELECT in the script (remove the Load) and look at the table created in the data model viewer. 

-Rob

[vinieme12 · Accepted solution]

Only execute the SQL load and check the field names, you don't need a preceding LOAD for  a SQL load

TempTable:

SQL
SELECT
cc.Legacy_Subline_Code,

(CASE WHEN cc.Legacy_Subline_Code = '240' THEN 'Other Health Care Professional Liability'
WHEN cc.Legacy_Subline_Code = '210' THEN 'Hospital Professional Liability'
WHEN cc.Legacy_Subline_Code = '230' THEN 'Physicians, Surgeons, and Dentists Professional Liability'
WHEN cc.Legacy_Subline_Code = '220' THEN 'Other Health Care Facilities Liability'
WHEN cc.Legacy_Subline_Code = '317' THEN 'Professional Liability - Other than Hospital, Health Care, Physicians, Surgeons and Dentists and Management Protection Program'
ELSE cc.Legacy_Subline_Code END) as Subline

lioc.External_Reference_Text,
SUM(fpp.Written_Premium_Amount) as GWP,
SUM(Earned_Premium_Amount) as Earned

FROM ... ;

Can you Also post a screenshot of the error message? ensure the field  cc.Legacy_Subline_Code   is available in the table aliased 'cc'   the field not found could also be a message returned from the Database and not Qlik

[teetoes]

Thank you for the suggestions. I tried the all uppercase field in the Load and it still didn't recognize it. 

I did get just the select statement to run but I had to revert my IF statements from the load to Case statements. However, my totals are not correct and I have no idea why there would be a difference.

Edit: It's actually just the "Open_Claims" column that is incorrect. The others match exactly.