topic Re: When i export the data from Pivot table into excel sheet in QlikView

When i export the data from Pivot table into excel sheet

Anonymous — Thu, 14 Aug 2014 14:09:54 GMT

Hi All,

I have one requirement that when i export the data from a pivot table from qlikview to excel. Is there any way the output in the excel also be a pivot format of the data. Thanks for your help in advance.

Re: When i export the data from Pivot table into excel sheet

Anonymous — Thu, 14 Aug 2014 14:56:39 GMT

Any help on this please

Re: When i export the data from Pivot table into excel sheet

ecolomer — Thu, 14 Aug 2014 15:14:24 GMT

No, the format are standard

Re: When i export the data from Pivot table into excel sheet

engishfaque — Thu, 14 Aug 2014 16:00:24 GMT

Dear Vinay,

Tricky way to copy/paste Pivot Table in Microsoft Excel. Copy/Paste Pivot Table from QlikView to Microsoft Word, and then import MS Document in MS Excel. Here are the three steps solution, please follow.

Steps 1: Go to QlikView

Pivot Table > Copy to Clipboard > Object.

Step 2: Go to Microsoft Office Word 2003

New Document > Paste (CTRL + V).

Step 3: Go to Microsoft Excel 2003

New Sheet > Menu Insert > Object > Tab Create from File > Browse "Word Document" > OK.

Should you require any help, please let me know.

Kind regards,

Ishfaque Ahmed

Re: When i export the data from Pivot table into excel sheet

Thu, 14 Aug 2014 16:04:59 GMT

Hi Vinay,

No as others have said that is the standard format, you can simply use the excel pivot capability on the exported data to achieve similar results though

hope that helps

Joe

Re: When i export the data from Pivot table into excel sheet

Anonymous — Fri, 15 Aug 2014 13:22:24 GMT

HI Ahmed,

Sorry that did not help me. Thanks a lot for your response.

Re: When i export the data from Pivot table into excel sheet

Anonymous — Fri, 15 Aug 2014 13:23:56 GMT

HI Joe,

Thanks for your response. So you are saying that once i export the data to excel ..do i need to modify the excel sheet to set up as Pivot table. Please guide me.

Re: When i export the data from Pivot table into excel sheet

giakoum — Fri, 15 Aug 2014 13:38:40 GMT

Or you can use a macro to do the job. This one below only exports to excel. But you can amend it to create a pivot. I have never done it, so please don't ask for help, this is a big task. Sorry!

SUB Excel

set obj = ActiveDocument.GetSheetObject( "CH01" )

w = obj.GetColumnCount

if obj.GetRowCount>1001 then

h=1000

else h=obj.GetRowCount

end if

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

objExcel.Worksheets(1).select()

objExcel.Visible = True

set CellMatrix = obj.GetCells2(0,0,w,h)

column = 1

for cc=0 to 8

objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text

objExcel.Cells(1,column).EntireRow.Font.Bold = True

column = column +1

c = 1

r =2

for RowIter=1 to h-1

for ColIter=0 to 8

objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text

'msgbox(CellMatrix(RowIter)(ColIter).Text)

c = c +1

r = r+1

c = 1

END SUB

Re: When i export the data from Pivot table into excel sheet

Anonymous — Fri, 15 Aug 2014 14:01:56 GMT

Its exporting the data to excel but the exported data is not in the format of pivot table....

Re: When i export the data from Pivot table into excel sheet

shree909 — Fri, 15 Aug 2014 15:14:55 GMT

Hi Vinay ,

I don't think this would be the solution. This would work for creating a pdf file.

The other way is u can create a report add the pivot table , ask the user to select the report or create a button to run the report as a pdf file .

Thanks

Re: When i export the data from Pivot table into excel sheet

suepenick — Mon, 20 Mar 2017 15:02:45 GMT

After struggling with this, this seems to be the easiest solution. If it is in an expression - the number formatting seems to work from the properties. But if there is a number in a dimension in a pivot table, when exported to excel it thinks it is text...

Solution in the load script multiply by 1 to force QV to assume this is a number.

example:

if(itemno='123456', 1,0) as MyItemFlag,

You will do this all the time - and you may want to sum the flag in the dimension field on export, but it thinks the 1 is text.,

type it

if(itemno='123456', 1*1, 0*1) as MyItemFlag,

when you export to excel, the 1's and zeros are numbers.

so if you have a project number field

ProjNo *1 as ProjectNo,

should do the Trick

I have tried constantly to have a displayed dimension be exported to excel as a number, and the only time it leaves it a number is if on load, QV thinks it is really a number (like an expression, where the data comes from is formatted as a number, or dates) - so you have to force it to think it's a number...the Num#(... stuff just doesn't seem to work on load or in the expression for exporting to excel- it does seem to work when displaying it in the charts though.