Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rmuhammad
Creator
Creator

Qlik having problem with some values, changing them to zero

Hi all

I am having challenges with the values in curr grade field. When ever Qlik finds a value 0E? (range 1- 300) it changes it to zero.

See attached example. Any suggestions to work around?? my suspicion is that it has something to do with Exponential  x to the power

data.PNGqlik.PNG

7 Replies
swuehl
MVP
MVP

You're right, the automatic number interpretation kicks in, use Text() to avoid this:

LOAD EMPLID, MONTH, YEAR, Text(GRADE2) as GRADE2, LEFT, LEN, RIGHT, MASK

FROM [EXPO ERR.xlsx] (ooxml, embedded labels);

sunny_talwar

Try this

LOAD EMPLID, MONTH, YEAR, Text(GRADE2) as GRADE2, LEFT, LEN, RIGHT, MASK

FROM [EXPO ERR.xlsx] (ooxml, embedded labels);

rmuhammad
Creator
Creator
Author

This issue is bit more complicated. Actual I am loading it from DB2 SQL and i used CHAR() but it didnt help. Only work around so far I found is to insert some character in value and after the load use text(replace()).

SELECT  CHAR(CURR_GRADE ) AS CH,

    CASE WHEN (CURR_GRADE LIKE '0E%') THEN CONCAT(CONCAT(LEFT(CURR_GRADE,2),'|'), RIGHT(CURR_GRADE,LENGTH(CURR_GRADE)-2)) END AS TEST ,

swuehl
MVP
MVP

Have you tried using a preceding LOAD with the Text() function?

shiveshsingh
Master
Master

Hi

You can try

Text(Grade2) as Grade2 while loading

rmuhammad
Creator
Creator
Author

in the load I have inserted pipe sign


CASE WHEN (CURR_GRADE LIKE '0E%') THEN CONCAT(CONCAT(LEFT(CURR_GRADE,2),'|'), RIGHT(CURR_GRADE,LENGTH(CURR_GRADE)-2)) END AS TEST ,



then used function Replace() to remove | character and applied Text(). This is the only successful work around I have found

stabben23
Master
Master

You are note using preceding LOAD, right? As Stefan suggests, this a very simple "work around".

You could solve many simular problem With Preceding LOAD.