topic Dowload to Excel - Numerics becoming Text in QlikView

Dowload to Excel - Numerics becoming Text

Fri, 04 Feb 2011 10:24:57 GMT

Can anyone assist with the issue of excel downloads where numerics are converted to text . I know that I can correct by error checking in Excel but surely there must be a way to ensure that downloaded QV data comes across correctly. The fields in error are calculated dimensions within a chart.

Dowload to Excel - Numerics becoming Text

vgutkovsky — Fri, 04 Feb 2011 18:37:05 GMT

If you want to make sure your numbers remain number on an export to Excel, wrap it in a num() function. Conversely, if you want it to be text, wrap it in a text() function.

Regards,

Dowload to Excel - Numerics becoming Text

Mon, 07 Feb 2011 09:30:05 GMT

Thanks Vlad,

I had already used the above ( here is the example)

=num(stockm.physical_qty*stockm.current_cost,'#,##0.##')

The strange thing is whole numbers are OK . Its when its not i.e. 150.24 that it throws out.

Rgds

Jeff

Dowload to Excel - Numerics becoming Text

vgutkovsky — Mon, 07 Feb 2011 15:57:06 GMT

Ah, I understand. I've seen this problem before and was baffled by it too. The way I ended up fixing it was disabling the default Send-to-Excel on the chart and instead creating a macro that sent it to Excel and was triggered by a button. I then applied the formatting directly in Excel. I actually like this type of export better because it gives you more granular control over the output formatting. For example:


SET XLApp = CreateObject("Excel.Application")
XLApp.Visible = False
SET XLDoc = XLApp.Workbooks.Add
SET XLSheet = XLDoc.Worksheets(1)
ActiveDocument.GetSheetObject("EXP1A").CopyTableToClipboard true
XLSheet.Paste XLSheet.Range("A1")
XLSheet.Range("B1:B5").NumberFormat = "$#,##0.00;($#,##0.00)"
XLSheet.Range("C1:C5").NumberFormat = "#,##0"
XLApp.Visible = True

The problem is that I doubt this will work over AJAX so if that's a requirement, this solution won't help you.

Cheers,

Dowload to Excel - Numerics becoming Text

Tue, 08 Feb 2011 11:27:31 GMT

Many Thanks , Has worked now!