Qlik having problem with some values, changing them to zero

[rmuhammad]

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

[swuehl]

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]

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]

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

[shiveshsingh]

Hi

You can try

Text(Grade2) as Grade2 while loading

[rmuhammad]

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]

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.