topic Re: How To Convert Exponential in QlikView

How To Convert Exponential

d4rlie891 — Tue, 16 May 2017 07:55:09 GMT

Hi,

Anyone can tell me how to Convert Exponential to Number?

I have tried these ways but none of them is Correct :

LOAD

SD_KEY,

NUM(SD_KEY) as SD_KEY1,

num(num#(SD_KEY),'################','.',',') as SD_KEY2,

Num(Replace([SD_KEY], ',', '.'), '(dec)') as SD_KEY3,

Num(SD_KEY, '(DEC)') AS SD_KEY4,

SD_SEQ_NUM

FROM [lib://MIS_Folder/Test DMW\BAF.xlsx]

(ooxml, embedded labels, table is Sheet1);

Thanks

Darmawan

Re: How To Convert Exponential

vinieme12 — Tue, 16 May 2017 09:41:48 GMT

have you tried just NUM()?

LOAD

SD_KEY,

NUM(SD_KEY,0) as SD_KEY1,

num(SD_KEY,0) as SD_KEY2,

Num(Replace([SD_KEY], ',', '.'), '(dec)') as SD_KEY3,

Num(SD_KEY, '(DEC)') AS SD_KEY4,

SD_SEQ_NUM

FROM [lib://MIS_Folder/Test DMW\BAF.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: How To Convert Exponential

Anil_Babu_Samineni — Tue, 16 May 2017 09:53:34 GMT

Can you tell us output as well after done the exponential

Re: How To Convert Exponential

swuehl — Tue, 16 May 2017 10:19:08 GMT

Qlik is only able to store 14 significant digits for your integers due to the internal storage format.

Your key value show more digits.

To store all digits, you need to use text format:

LOAD

SD_KEY,

Text(SD_KEY) as TextKEY,

NUM(SD_KEY) as SD_KEY1,

num(num#(SD_KEY),'################','.',',') as SD_KEY2,

Num(Replace([SD_KEY], ',', '.'), '(dec)') as SD_KEY3,

Num(SD_KEY, '(DEC)') AS SD_KEY4,

SD_SEQ_NUM

FROM [lib://MIS_Folder/Test DMW\BAF.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: How To Convert Exponential

d4rlie891 — Tue, 16 May 2017 10:26:33 GMT

Hi Stefan,

Thanks for your reply.

I have tried using TEXT(SD_KEY) but the output is same as the original SD_KEY.

LOAD

SD_KEY,

TEXT(SD_KEY) as SC_KEY0,

NUM(SD_KEY) as SD_KEY1,

num(num#(SD_KEY),'################','.',',') as SD_KEY2,

Num(Replace([SD_KEY], ',', '.'), '(dec)') as SD_KEY3,

Num(SD_KEY, '(DEC)') AS SD_KEY4,

SD_SEQ_NUM

FROM [lib://MIS_Folder/Test DMW\BAF.xlsx]

(ooxml, embedded labels, table is Sheet1);

The Output:

Re: How To Convert Exponential

d4rlie891 — Tue, 16 May 2017 10:27:50 GMT

Hi Vineeth,

the output that I want is :

SD_KEY

4453771122906050

4453771125926360

Thanks

Re: How To Convert Exponential

swuehl — Tue, 16 May 2017 10:45:34 GMT

Seems like Qlik only see the internal format Excel uses for your key values (it's already formatted differently in your sheet, have a look at the cell format properties).

If you manage to reformat your values in excel, you can load the key values in same way in Qlik.

LOAD SD_KEY,

SD_SEQ_NUM,

SD_KEY_TEXT

FROM

[BAF_2.xlsx]

(ooxml, embedded labels, table is Sheet1);