topic Re: Qlik having problem with some values, changing them to zero in QlikView

Qlik having problem with some values, changing them to zero

rmuhammad — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Qlik having problem with some values, changing them to zero

swuehl — Tue, 23 Jan 2018 14:46:45 GMT

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);

Re: Qlik having problem with some values, changing them to zero

sunny_talwar — Tue, 23 Jan 2018 14:47:22 GMT

Try this

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

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

Re: Qlik having problem with some values, changing them to zero

rmuhammad — Tue, 23 Jan 2018 19:40:39 GMT

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 ,

Re: Qlik having problem with some values, changing them to zero

swuehl — Thu, 01 Feb 2018 23:29:54 GMT

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

Re: Qlik having problem with some values, changing them to zero

shiveshsingh — Fri, 02 Feb 2018 05:49:01 GMT

You can try

Text(Grade2) as Grade2 while loading

Re: Qlik having problem with some values, changing them to zero

rmuhammad — Fri, 02 Feb 2018 13:52:48 GMT

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

Re: Qlik having problem with some values, changing them to zero

stabben23 — Fri, 02 Feb 2018 14:00:42 GMT

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.