Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

d4rlie891
New Contributor III

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 :

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

Result.JPG

Thanks

Darmawan

6 Replies
vinieme12
Esteemed Contributor II

Re: How To Convert Exponential

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

Can you tell us output as well after done the exponential

MVP
MVP

Re: How To Convert Exponential

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
New Contributor III

Re: How To Convert Exponential

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:

Result2.JPG

d4rlie891
New Contributor III

Re: How To Convert Exponential

Hi Vineeth,

the output that I want is :

 

SD_KEY
4453771122906050
4453771125926360

Thanks

MVP
MVP

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.

LOAD SD_KEY,

     SD_SEQ_NUM,

     SD_KEY_TEXT

FROM

[BAF_2.xlsx]

(ooxml, embedded labels, table is Sheet1);

2017-05-16 12_45_04-QlikView x64 - [C__Users_Stefan_Downloads_comm260311_Product.qvw_].png