topic Re: Data converted to scientific notation in QlikView

Data converted to scientific notation

cliff_clayman — Thu, 16 Mar 2017 16:57:16 GMT

I have a field that is a text field in my SQL table. I went to great lengths to ensure this field gets assigned as text in the database. When I add it to my Qlikview report, some of the values are still showing as scientific notation. I load many other fields into the report and I just do a SELECT * FROM TableName. How can I LOAD the field as TEXT in the script and just call out the field(s) I want to make as text?

Re: Data converted to scientific notation

sunny_talwar — Thu, 16 Mar 2017 16:59:05 GMT

May be load like this:

LOAD Num(GLAcct) as GLAcct;

SELECT GLAcct FROM dbo.GRIR_Main;

Re: Data converted to scientific notation

cliff_clayman — Thu, 16 Mar 2017 17:05:31 GMT

How do I load all the rest of the fields without specifically listing them all out?

Re: Data converted to scientific notation

swuehl — Thu, 16 Mar 2017 17:05:37 GMT

Or do you want to keep it as text?

LOAD Text(GLAcct) as GLAcct;

SELECT GLAcct FROM dbo.GRIR_Main;

Re: Data converted to scientific notation

sunny_talwar — Thu, 16 Mar 2017 17:08:51 GMT

May be like this

LOAD *,

Num(GLAcct) as GLAcct1;

SELECT GLAcct FROM dbo.GRIR_Main;

DROP Field GLAcct;

RENAME Field GLAcct1 as GLAcct;

Re: Data converted to scientific notation

sunny_talwar — Thu, 16 Mar 2017 17:09:48 GMT

Have not tried this, but you can give this a try

TempTable:

LOAD Num(0) as GLAcct

AutoGenerate 0;

SELECT GLAcct FROM dbo.GRIR_Main;

DROP Table TempTable;

Re: Data converted to scientific notation

cliff_clayman — Thu, 16 Mar 2017 17:09:50 GMT

I do want to keep it as text. When I use Num(), I lose some of the data. When I use Text(), I still see scientific notation.

Re: Data converted to scientific notation

cliff_clayman — Thu, 16 Mar 2017 17:13:09 GMT

That does not work as I do not get any fields to display.

Re: Data converted to scientific notation

sunny_talwar — Thu, 16 Mar 2017 17:18:45 GMT

No fields show up? Not even GLAcct

Re: Data converted to scientific notation

sunny_talwar — Thu, 16 Mar 2017 17:19:09 GMT

My bad, try this

TempTable:

LOAD Num(0) as GLAcct

AutoGenerate 0;

SELECT * FROM dbo.GRIR_Main;

DROP Table TempTable;

Re: Data converted to scientific notation

cliff_clayman — Thu, 16 Mar 2017 17:19:16 GMT

That is correct.

Re: Data converted to scientific notation

srchilukoori — Thu, 16 Mar 2017 17:24:48 GMT

In the above script, the second load is concatenated to first and gets dropped. You need to use a 'No Concatenate' statement.

Re: Data converted to scientific notation

sunny_talwar — Thu, 16 Mar 2017 17:31:57 GMT

Thanks for pointing out, but the script above is SELECT * and not SELECT GLAccts... so in this case it isn't required anymore.

Best,

Sunny