QlikView treats 'numeric' strings as numeric?? HELP!

[#1]

Hi,

I'm tearing my hair out trying to solve a problem with QlikView maybe being too clever for its own good.

I'm trying to get data out of an Oracle database via ODBC which has as its candidate key a character account code (CONSUMERID - defined as CHAR(8)) but which frequently has "numeric" values.

So I do (after defining the ODBC connection)

SQL SELECT CONSUMER.CONSUMERID, ...

FROM CONSUMER;

For account codes that are similar but have different numbers of zeroes and/or spaces at the beginning, QlikView seems to think that all records have the same account code (randomly, it seems whichever one QlikView finds first it keeps).

ie " 033971 " and "033971 " and "33971 " are all treated as "33971 ".

This is confirmed when I try something like

SQL SELECT CONSUMER.CONSUMERID, TRANSLATE(CONSUMER.CONSUMERID, ' ', '!') AS CONSUMERID_ALT, ...

FROM CONSUMER;

Then I get:

CONSUMERID CONSUMERID_ALT

33971 !033971!

33971 033971!!

33971 33971!!!

How do I get QlikView to extract the exact CHARACTER value of CONSUMERID ???

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Certainly, these things happen in QlikView. Do something like

LOAD TEXT(CONSUMERID) AS CONSUMERID; SQL SELECT CONSUMER.CONSUMERID FROM CONSUMER;

Load wizard in the bottom part of the script editor may help you adding all fields to a LOAD clause.

Hope this helps!

[#3]

Tried this, it works, but it still cannot tell the difference between an account code of

" 033971 " (with leading space)

and

"033971 " (without leading space)

How would I make sure that it did?

[#4]

Make sure that you in the application also show the field as

text(fieldname)

as there usually is another interpretation done when viewing the field.

[#5]

You are right in that Qlikview will see these values as the same and regard them all as the first variant it finds. The way forward is text(fieldname) to force it to text but you could also attach a non-numeric character to the data being retrieved via SQL (' 'X' & fieldname as Xfieldname)and then strip it off using substring (eg left, mid, right).

Regards,

Gordon