Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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.
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
We actually look for a solution without any extra buttons ... we try to use the export button shown in the header of the table.
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.
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.
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
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.
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.
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 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.