Leading Zeros, load text(...) and functions in sql select(...)

[#1]

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.

[jvitantonio · Accepted solution]

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

[#3]

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;