Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having an issue with how qlikview reads fields from the database. I know SQL usually is not case sensitive when it comes to field names and table names.
Unfortunately we just upgraded our software and server so we now have a new database (Same tables and field names). In this new database instead of the fields being all lower case they are now like this example:
Old Field: sendalertcomplete
New Field: SendAlertComplete
Now since Qlikview reads the database as case sensitive, Qlikview cannot find any of my fields. Remembering where the letters are capitalized at will be a huge pain, especially when I have 30+ tables in some of my documents.
Does anyone know if there is a way to turn off case sensitivity when reading from SQL or transforming the field names so that they are lower case? I know doing the following is an option, but this will be cumbersome to do for every field for every table. (SendAlertComplete as sendalertcomplete)
Edit: Ok I tested and I dont have to do Table as table in the SQL Select portion of the scripts. Simply stating table pulls the information still. This is still annoying though as I am not unable to simply say Select * from; I now have to list every field from the table to get the proper case...
That saved me probably several hours worth of work, Thank you very much sir.
D'Oh! I've even used that one myself previously!!
Do you need to turn this off after you completed this step?
Klangley had a good question, do I need to apply this once at the begining of the document, or before every Load statement that I want it to affect?
It's a directive. It remains in effect until changed by a subsequent FORCE statement. So once is good enough.
See "Force" in the help or Ref Guide for options.
-Rob
Rob,
Does this Force Lower only the field names, or also force lowers the contents of the fields themselves?
I need JUST the field names to be lower case, and the contents to be mixed.
Ouch. It does indeed change the values to lower case as well as the field name.
-Rob
Hi Rob Wunderlich,
I tried to use Force Case Upper but it doesn't work for me.
Am I doing something wrong ?
Regards
Here's another solution I've discovered if the FORCE option doesn't suit your needs.
ODBC CONNECT TO {connection}; //or oledb
SQLTableColumns:
load *;
SQLCOLUMNS; //returns info on all poss tables and columns via the connection
//Build SQL Commands
//This effectively concatenates a list of all the fields in that table and builds a dynamic select string (limited only to the tables you need by the MATCH command)
SQLSelectCommands:
LOAD
TABLE_NAME,
'Select ' & concat(lower(COLUMN_NAME),',') & ' from ' & TABLE_NAME AS ColList
resident SQLTableColumns
where Match(TABLE_NAME,'{table1}','{table2}','{table3}' ...) > 0
Group By TABLE_NAME;
DROP TABLE SQLTableColumns; //don't need this anymore
//when you are ready to do the select * command, replace as follows for each table
LET cmdSQL = Lookup('ColList', 'TABLE_NAME', '{table}'); // where {table} is your source SQL table
Order1:
LOAD {QV LOGIC as previous}
SQL $(cmdSQL) where clause ...;
I have only tested this briefly, but seems to work. Hope it helps.
flipside
EDIT: This works with some drivers and not others. My Informix driver can return either all UPPERCASE or all LOWERCASE by using the FORCE CASE command, but default is lowercase - not mixed - and cannot FORCE mixed, even if ColList above uses column name aliasing. With Microsoft Excel driver though, mixed cases are possible and this works.
With reference to the RENAME fields option I gave earlier, it will generate an error if you attempt to rename a field to the same name, so test for this first to prevent the error ...
sub RenameFlds(SQLQuery, qvdName)
tmp:
SQL $(SQLQuery);
For f = 1 to NoOfFields('tmp')
let vFieldOld = FieldName($(f),'tmp');
let vFieldNew = Lower('$(vFieldOld)');
If $(vFieldNew) <> $(vFieldOld) then
RENAME FIELD $(vFieldOld) to $(vFieldNew);
ENDIF;
Next f;
STORE tmp INTO '$(tblName)'.qvd (qvd);
DROP Table tmp;
end sub
In the code above I have placed the logic into a sub routine to create a temp qvd within the load script which can then be called multiple times as follows ...
LET SQLcmd = 'SELECT * FROM {tbl}';
CALL RenameFlds('$(SQLcmd)','Order1');