topic Re: Export to Excel - Multiple Sheets over Dimension in QlikView

Export to Excel - Multiple Sheets over Dimension

Anonymous — Mon, 06 Jun 2011 23:08:18 GMT

Hello all,

What I'm trying to do is to take a table file which would normally be exported as one sheet in excel and instead to export it to multiple sheets whenever a particular dimension changes.

Here is a sample of what some data might look like:

@1	@2	@3
A	121	Sampletext1
A	122	Sampletext2
A	123	Sampletext3
B	121	Sampletext4
B	122	Sampletext5
B	123	Sampletext6
C	121	Sampletext7
C	122	Sampletext8

and here is how I'd like the Excel file to look, whereby each tab is labeled as the different change in dimension and only that relevant data shows up:

Can anyone provide some advice or sample code that might guide me to this type of solution?

Thanks,

- Peter

Re: Export to Excel - Multiple Sheets over Dimension

Anonymous — Tue, 07 Jun 2011 01:27:06 GMT

Hey Peter,

Here's a macro example I whipped together with your data. It works alright but the sheets get a bit mixed up when adding more values to "reduce" over. At least you get the idea.

Export to Excel - Multiple Sheets over Dimension

Anonymous — Tue, 07 Jun 2011 16:30:45 GMT

Johannes,

Thanks for the quick response. This is helpful.

I was wondering how this might be replicated on a table that already has selections applied? When I run this macro it seems to go through and create it's own selections, however, if I have selections already applied to a table/chart I'd like to be able to export that and do something similar to this.

Thanks,

- Peter

Re: Export to Excel - Multiple Sheets over Dimension

Anonymous — Wed, 08 Jun 2011 02:52:03 GMT

Hey Peter,

Not quite sure what you mean. If I select B&C and export, I get an Excel file with B and C in different sheets. Do you want to keep the original selection after completing the macro? In that case you could create a bookmark in the macro before looping over the values, and recall it after finishing before dropping the bookmark.

I modified the macro quickly. See attached example.