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

    How To Convert Exponential

    Darmawan Suria

      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

        • Re: How To Convert Exponential
          Vineeth Pujari

          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
            Anil Babu Samineni

            Can you tell us output as well after done the exponential

            • Re: How To Convert Exponential
              Stefan Wühl

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

                • Re: How To Convert Exponential
                  Darmawan Suria

                  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

                    • Re: How To Convert Exponential
                      Stefan Wühl

                      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