Hello,
amriteshraj wrote: This is because irrespective of in which case you give the column name, it will always return the result of column which is in upper case. |
Actually, this is not correct, and thanks for bringing it to our attention so further users can get the right thing. It's a very common mistake in the early stages of using QlikView, and it keeps happening to me once in a while.
When you retrieve data from a database, you are always using a driver external from QlikView, either a ODBC or an OLE DB (with the exception of the SAP or SalesForce Connectors developed by QlikView and certified by their corresponding vendors). These drivers have different settings and depending on the vendor, even for the same data base engine, these settings may vary.
In this case, the SQL statement you are using allows you to use lowercase field names even when they are actually uppercase in the database. Some other drivers (or some settings in the driver) may throw a "field not found" error when doing the SELECT. QlikView is case sensitive for both field names and values.
Regardless the SQL sentence, the field names loaded into the QlikView Document are always, by default, the same as they are returned by the database. So if the fields are all uppercase in the source, the will be loaded uppercase.
My mistake in this thread was not recommending you to specify a LOAD along with your SELECT (as I attached in my application, although I was using INLINE instead). But I do it now: do ALWAYS a LOAD for every table you are loading into QlikView. It's not required by the syntax but this will save development time and will help you debug this kind of errors, besides allowing you, of course, to rename, use functions, create new fields and so. Whether you specify the LOAD or not, QlikView will do it anyway.
So properly written, your script should look like this:
Table1:
LOAD fetch_site, // this one is likely lowercase because you manually renamed it in the SQL statement
WDAYS##1,
WDAYS##2,
WDAYS##3,
WDAYS##4,
WDAYS##5,
WDAYS##6,
WDAYS##7;
SQL SELECT SHOP_SITE as fetch_site, wdays##1, wdays##2, wdays##3, wdays##4, wdays##5, wdays##6, wdays##7
FROM SHOP_CALENDAR;
If you
LOAD wdays##1 // lowercase
instead of
LOAD WDAYS##1 // UPPERCASE
You get an error (field not found), because the field name is not properly written.
Any further reference to this field, thus, must be done uppercase since it's the field actually loaded into QlikView (you will see that using the Table Viewer or CTRL + T). And your query analyzer will likely show likewise the field name uppercase. According to your script, it is interpreted by QlikView as follows:
table1:
LOAD *; // This line is what QlikView is doing
SQL SELECT SHOP_SITE as fetch_site,wdays##1 ,wdays##2 ,wdays##3 ,
wdays##4 ,wdays##5 ,wdays##6 ,wdays##7
FROM SHOP_CALENDAR;
The table above will be put into memory all field names in the same case than in the source database, because you haven't done any renaming.
So it's not that this is because it's irrespective of the case, but because the field name is actually uppercase, and in QlikView, field names and values are case sensitive.
It's very important to get it clear, to avoid errors in further developments and prevent further confusions.
Hope that makes sense and helps.
Miguel Angel Baeyens
BI Consultant
Comex Grupo Ibérica
.png "Miguel_Angel_Baeyens"){kind=avatar}
