Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted
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);

Highlighted

Re: How To Convert Exponential

Can you tell us output as well after done the exponential

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
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);

Highlighted
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

New Contributor III

Re: How To Convert Exponential

Hi Vineeth,

the output that I want is :

 

SD_KEY
4453771122906050
4453771125926360

Thanks

Highlighted
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