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
you can use:
if(isNull(used_entry), 'value', text(used_entry)) as used_entry
Hi,
Try with this
load text([used_entry]) as UsedEntry;
sql select nvl(possible_entry1,possible_entry2) as used_entry from table;
May be its because same field name usage.
Celambarasan
load text([used_entry]) as UsedEntry did not work, sorry.
I got the same error as before.
As for moving the logic out of the sql part into the load-statement as Federico Levi suggested, this would be the last resort. The nvl(..) line is just an example from the whole script, and i would rather avoid rewriting every sql-statement in corresponding functions in qlikview.
What I do not understand is that the error comes up when executing the sql select, and not the load-statement.
If i leave the load-Statement out completeley, the sql executes without error (but mixes the '1' and '001' up).
Hi,
What error you are getting, attach the script or sample file.
Regards,
jagan.
The error i get is "Field not found"
The script (reduced to the important part):
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;
// does not work
load TEXT(ziffer) as ziffer;
sql select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;
Hi,
select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;
is the above query executes correctly in the db. Make sure.
There is nothing wrong in your script;
Regards,
Jagan.
sql executes correctly (see below), and if i leave out the "load"-statement, the scripts executes without error (but mixing up 0003 and 3).
SQL> select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;
ZIFFER
-------
31138
31101
0003TK
0003
0003TK
0003
3
[....]
1617 Zeilen wurden ausgewählt.
SQL> spool off
Hi,
Try like this
TempTable:
sql select
nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer,
1 AS Temp
from m1bhlst;
TableName:
LOAD
Text(ziffer) AS ziffer
RESIDENT TempTable;
DROP TABLE TempTable;
Hope this helps you.
Regards,
Jagan.
I thought that this way the values with leading zeros would be thrown together in "TempTable" already, but I tried it anyway, and now i get the same error "Field not found <ziffer>" during the load-Statement in "TableName:".
Right now i am really confused, i even get this error with:
TempTable:
select 'test' as a from dual;
load a as b resident TempTable;
... which is almost straight out of the QV-Help to the "Load"-Command. I think I am doing something really wrong, but i cannot see it.