Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Why do I get errors on this LOAD SELECT?

What am I doing wrong?  I keep getting errors and I just don't know why.  I have tried different things and nothing is working.  I am sure it is something easy I am missing that I will feel silly for.

Count:

LOAD

number,

date,

store,

date & ' - ' & store as StoreDateKey

;

SQL SELECT

number,

date,

store

FROM PLACE.PROD.INFO

WHERE Year(date) = $(vCurrentYear);

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Many flavours of SQL convert the field names to upper case.  Try something like this :

Count:

LOAD

NUMBER     as number ,

DATE           as date ,

STORE        as store ,

date & ' - ' & STORE as StoreDateKey

;

SQL SELECT

NUMBER,

DATE,

STORE

FROM PLACE.PROD.INFO

WHERE Year(date) = $(vCurrentYear);

View solution in original post

15 Replies
oknotsen
Master III
Master III

What is the exact error you are getting?

May you live in interesting times!
Anonymous
Not applicable
Author

Here is the error I get...

Field not found - <number>

SQL SELECT

   number,

   date,

   store

FROM

     PLACE.PROD.INFO

WHERE

     Year(date) = 2016

Anonymous
Not applicable
Author

Many flavours of SQL convert the field names to upper case.  Try something like this :

Count:

LOAD

NUMBER     as number ,

DATE           as date ,

STORE        as store ,

date & ' - ' & STORE as StoreDateKey

;

SQL SELECT

NUMBER,

DATE,

STORE

FROM PLACE.PROD.INFO

WHERE Year(date) = $(vCurrentYear);

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Check whether there really is a field called number in your database table. You can peek in your DBMS by pressing the Select... button in the script editor. You may have to provide the connection credentials again. After that, you'll be presented with a dialog that allows you to view table layouts. Check table PLACE.PROD.INFO.

Best,

Peter

Peter_Cammaert
Partner - Champion III
Partner - Champion III

And an additional check you can perform: the WHERE clause may be too restrictive in that it forces your DBMS to return nothing at all. Comment out the WHERE clause of the SELECT statement and check whether the error message goes away.

Best,

Peter

Anonymous
Not applicable
Author

Thank you that worked!

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Bill, that would mean that this one would work as well?

Count:

LOAD

NUMBER     as number ,

DATE           as date ,

STORE        as store ,

date & ' - ' & STORE as StoreDateKey

;

SQL SELECT

number,

date,

store

FROM PLACE.PROD.INFO

WHERE Year(date) = $(vCurrentYear);

Anonymous
Not applicable
Author

Yup that would work fine as well.

But if SQL is going to convert field names to upper case then I find it easier for people to understand if I script it as upper case.

Another option in the sql is to rename the field in double quotes and that will force it to whatever case you have in the quotes, in the script below that is lower case:

SQL SELECT

number     as "number" ,

date          as "date" ,

etc

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Thanks Bill.