How To Convert Exponential

[d4rlie891]

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

[vinieme12]

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

[Anil_Babu_Samineni]

Can you tell us output as well after done the exponential

[swuehl]

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

[d4rlie891]

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:

[d4rlie891]

Hi Vineeth,

the output that I want is :

SD_KEY

4453771122906050

4453771125926360

Thanks

[swuehl]

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