Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
d4rlie891
Contributor III
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
Champion II
Champion II

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

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)
swuehl
MVP
MVP

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

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

Hi Vineeth,

the output that I want is :

 

SD_KEY
4453771122906050
4453771125926360

Thanks

swuehl
MVP
MVP

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