Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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 ???

Tags (1)
1 Solution

Accepted Solutions

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

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!

4 Replies

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

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!

Not applicable

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

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?

Not applicable

SV:Re: QlikView treats 'numeric' strings as numeric?? HELP!

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.

Not applicable

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

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

Community Browser