Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
sorry for opening the nth Thread about coping with leading zeros, but i have trouble on the finishing line with the information i found so far.
I found a few threads concerning loading data when keys can be "1", "01" or "001" an mean different things.
The (almost) canonical answer to the question i found is:
"load text(column) as column;
sql select column from table;"
For this example (extracting a column 1:1 into qlikview), it works perfectly.
However, i have a slight problem in my script, as a lot of functions (especially nvl, decode, ltrim/rtrim) are called in sql.
For example, one line in my script right now is:
sql select nvl(possible_entry1,possible_entry2) as used_entry from table;
(nvl is an oracle function that gives possible_entry1 if it is not NULL and possible_entry2 otherwise).
Now i have the same problem again: possible_entry1 and possible_entry2 can both contain "1" and "001" meaning different things.
however:
load text(used_entry) as used_entry;
sql select nvl(possible_entry1,possible_entry2) as used_entry from table;
gives an error when executing the "sql"-Line:
Field not found - <used_entry>
How can I apply the Text-Function in this example?
"load text (nvl(...))" does not work because the Qlik-View does not know about nvl, this part is done on the oracle-side.
GHR, I think I got the problem.
Qlik is CASE SENSITIVE with the field names and when you use "... as used_entry" it converts it to USED_ENTRY. So try this:
load text(USED_ENTRY) as used_entry;
sql select nvl(possible_entry1,possible_entry2) as used_entry from table;
Let me know how it goes
qlikuser14, you are right. I was so concentrated on the nvl(..) call as the difference between the working statement and the other that i missed the real problem (that the working part had all fieldnames completely in uppercase).
The sample script works as below, preserving '003' and '3' as distinct values.
I will now go to the real script, but i do not expect any problems. Thanks a lot to all of you, especially jagan and qlikuser14.
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YY';
SET TimestampFormat='DD.MM.YY hh:mm:ss[.fff]';
CONNECT TO [Provider=MSDAORA.1;Password=xxxx;User ID=xxxx;Data Source=m1db_tcp_entw];
// works
load TEXT(ORT_VORWAHL) as ORT_VORWAHL;
sql select ORT_VORWAHL from m1ort;
// would not work
//load TEXT(ziffer) as ziffer;
//sql select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;
// this does work
load TEXT(ZIFFER) as ZIFFER;
sql select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ZIFFER from m1bhlst;