topic Excel exports field with value 00149E5 as 0014900000 in QlikView

Excel exports field with value 00149E5 as 0014900000

Anonymous — Thu, 10 Dec 2009 16:49:32 GMT

When exporting a table with excel containing the dimension value: 00149E5 the export contains the value 0014900000

It seems that Excel interprets the E5 as 00000 ...

Does anyone have any suggestions how to fix this?

Excel exports field with value 00149E5 as 0014900000

Thu, 10 Dec 2009 17:04:18 GMT

I had to do something similar when I had to export 3 tables into one excel sheet. I had to use a macro as the formatting was incorrect.

If you try this:



 set XLApp = CreateObject("Excel.Application") 'Define Object
 XLApp.Visible = True 'Visible set as true
 set XLDoc = XLApp.Workbooks.Add 'Open new workbook

 ' Get TB1
 set table = ActiveDocument.GetSheetObject("CH02")
 ' Copy table and paste into Excel
 set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted
 table.CopyTableToClipboard true 'Copy data to Clipboard
 'Format cells and pastespecial
 XLSheet.Columns("B:F").NumberFormat = "@" 'Select the range you want to format as text
 XLSheet.Columns("K:K").NumberFormat = "@" 'Select the range you want to format as text
 XLSheet.Range("A1").Select 'Select field where data should be pasted
 XLSheet.PasteSpecial -4163 'According to msdn.com -4163

assign the macro to a button and that should work

Hope that helps

mike

Excel exports field with value 00149E5 as 0014900000

Anonymous — Thu, 10 Dec 2009 17:30:25 GMT

We actually look for a solution without any extra buttons ... we try to use the export button shown in the header of the table.

Excel exports field with value 00149E5 as 0014900000

Thu, 10 Dec 2009 18:44:37 GMT

Are you willing to manipulate in Excel? If you are, use the Excel function right(field,7).

If not, please attach your document or an example and I will have a look.

Excel exports field with value 00149E5 as 0014900000

amars — Thu, 10 Dec 2009 18:47:36 GMT

This thing happens when if the type of value u r exporting is a number , if u convert it into a string in Qlikview & then export it , It will come properly & not with E style of number.

Excel exports field with value 00149E5 as 0014900000

Anonymous — Thu, 10 Dec 2009 18:50:19 GMT

This is also what I thought ... but I can't find a way of changing the type of a dimenstion ... only for expressions I can change this.

We don't want to change the type of the data itself for our QlikView application ... only something to change the type for excel to have the correct display.

An sample is easy ... have a numeric dimension containing 00149E5 and export this to Excel ... at this point the display of the field becomes 0014900000

Excel exports field with value 00149E5 as 0014900000

Thu, 10 Dec 2009 22:29:13 GMT

The only solution I could find is to use a calculated dimension instead of the dimension itself and display ='"'&Dimension&'"'. This will then give you "00149E5" which Qlikview will recognise as text.

I am a little worried that you want to display numerics in the same dimension. If that is the case let me know and I will dig a little more.

Excel exports field with value 00149E5 as 0014900000

Fri, 11 Dec 2009 16:05:10 GMT

Had one more thought, you could use a calculated dimension along the lines of:

if(len(KeepChar(dimension,'E'))>0,'"'&dimension&'"',dimension)

This will leave the dimension unchanged unless there is an 'E' in it somewhere in which case it will add quotation marks around it.

Excel exports field with value 00149E5 as 0014900000

Sun, 13 Dec 2009 19:22:38 GMT

Did this help? If not please send me a copy of your model with only a little data and I will have a look whether I can figure something out.

Excel exports field with value 00149E5 as 0014900000

Anonymous — Sun, 13 Dec 2009 22:30:28 GMT

Excel will interpret 149E5 as 149 * 10^5 or 149 * 100000 = 14900000.

Have you tried casting your dimension field as text in the script of your qlikview document:

text(Fieldname) as Fieldname

No QlikView on this machine so I'm unable to test it.