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

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

Mon, 20 Feb 2012 16:46:09 GMT

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.

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

Mon, 20 Feb 2012 18:25:28 GMT

you can use:

if(isNull(used_entry), 'value', text(used_entry)) as used_entry

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

CELAMBARASAN — Tue, 21 Feb 2012 05:39:31 GMT

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

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

Tue, 21 Feb 2012 09:41:30 GMT

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).

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

jagan — Tue, 21 Feb 2012 09:45:49 GMT

Hi,

What error you are getting, attach the script or sample file.

Regards,

jagan.

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

Tue, 21 Feb 2012 10:00:10 GMT

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;

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

jagan — Tue, 21 Feb 2012 10:08:58 GMT

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.

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

Tue, 21 Feb 2012 10:17:36 GMT

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

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

jagan — Tue, 21 Feb 2012 10:23:06 GMT

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.

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

Tue, 21 Feb 2012 11:27:46 GMT

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.

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

jvitantonio — Tue, 21 Feb 2012 11:41:02 GMT

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

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

Tue, 21 Feb 2012 11:59:31 GMT

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.

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;