topic Re: Export to excel in QlikView

Export to excel

sona_sa — Mon, 27 Jan 2020 13:11:30 GMT

Hi Team,

I have a pivot table with some 5 Dimension and 1 Expression. Now I want to export the data in excel. I am able to download the excel, But the Expand and collapse option I am missing.

Do we have that type of macro or functionality in QlikView, So we can export the data with expand and collapse option. For more please find the attached QVW. Please share the script in notepad, So I can copy that and use in my QVW.

Please do the needful.

Thanks & Regards,

Re: Export to excel

Frank_Hartmann — Mon, 27 Jan 2020 13:45:13 GMT

this might be helpful

https://community.qlik.com/t5/New-to-QlikView/Pivot-table-export-to-excel/td-p/812223

Re: Export to excel

sona_sa — Tue, 28 Jan 2020 04:53:32 GMT

Hi Frank,

Thanks for reply, But after exporting the data, I want collapse and Expand option in Excel. Just like what ever we are doing in QlikView. That + and - button, which is available in QlikView Pivot table.

Hope you got my points.

Regards,

Amit Kumar

Re: Export to excel

Victor_Alumanah — Tue, 28 Jan 2020 11:04:26 GMT

1 option is to export it to Excel and create a pivot table, please see attached.

Re: Export to excel

sona_sa — Tue, 28 Jan 2020 12:39:23 GMT

Hi Victor,

Thanks for the same. But it is manually done in Excel. Can you describe the steps.

Also I want to know is there any possibilities of Macro to do the same through from QlikView while exporting the data to excel.

Regards,

Re: Export to excel

Victor_Alumanah — Tue, 28 Jan 2020 14:29:51 GMT

I don't know if it is possible to do this using a macro, but below are the manual steps to recreate in excel.

Select all data(CTRL +A)
On menu select Insert, then Pivot
A prompt called “Create pivot table” will appear, click ok
A new sheet will appear, click on cell A3
On the right of the screen will be a list of fields
Drag “Count” to bottom right cell called values
Drag all the others in the right order to “Row labels” (near where you put count)
Right click pivot table and select Pivot table options
On the the display tab select the option Classic pivot table layout
Right click cell A7 and unselect Subtotal, and then do the same for the other columns(B7 e.t.c)