6 Replies Latest reply: May 16, 2017 6:45 AM by Stefan Wühl

# How To Convert Exponential

Hi,

Anyone can tell me how to Convert Exponential to Number?

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

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

have you tried just NUM()?

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

Can you tell us output as well after done the exponential

• ###### Re: How To Convert Exponential

Hi Vineeth,

the output that I want is :

 SD_KEY 4453771122906050 4453771125926360

Thanks

• ###### Re: How To Convert Exponential

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

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

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

Hi Stefan,

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

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

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.

SD_SEQ_NUM,

SD_KEY_TEXT

FROM

[BAF_2.xlsx]

(ooxml, embedded labels, table is Sheet1);