Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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.

1 Solution

Accepted Solutions
jvitantonio
Valued Contributor III

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

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

11 Replies
Not applicable

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

you can use:

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

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

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

Not applicable

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

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

MVP
MVP

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

Hi,

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

Regards,

jagan.

Not applicable

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

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;

MVP
MVP

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

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.

Not applicable

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

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

MVP
MVP

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

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.

Not applicable

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

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.

Community Browser