4 Replies Latest reply: Jan 27, 2010 10:05 AM by Gordon Savage RSS

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

    EdwardLee

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