topic Re: Load tables from different Excel sheets with different sheet names? in QlikView

Load tables from different Excel sheets with different sheet names?

Fri, 21 Feb 2014 06:04:59 GMT

Hi,

Here is the problem I am facing:

I have to load multiple excel files from the same folder. So I am using the " *.xlsx " function in my script -

SALES:

LOAD [Sales Order number],

[Excise Invoice No],

[Excise Date],

[Quantity Delivered],

[Gross Sales Amount],

FROM

(ooxml, embedded labels, header is 1 lines, table is [Sales Drytech 2013-2014]);

The problem is that the table name (ie the sheet name) of all the excel files is different.

So, i have to manually change the sheet in each of the excel files individually.

Note: The sheet name is automatically named as name of the Excel file.

Is there any way where the different table name doesnt need to be changed?

Re: Load tables from different Excel sheets with different sheet names?

Gysbert_Wassenaar — Fri, 21 Feb 2014 06:55:26 GMT

See this discussion: Loading from multiple Excel files and multiple sheets

Re: Load tables from different Excel sheets with different sheet names?

Sun, 23 Feb 2014 02:36:27 GMT

I Dont know how to respond to you using my user name. U helped me with this expression a Month ago and it wroked fine

=Count({<[Occurence No]={'=sum(NET_INDEMNITY_PAID)>0 and sum(NET_INDEMNITY_PAID)<1000000 and Year=Max(CloseAdmin_Year)'},[Line of Business]={'HCS','PSL'}>} DISTINCT [Occurence No])

But ir doesnt work for Max(Year)-1. Any Suggestion.

Please reply to (Ray HDKJSH) with any suggestions how can I make it work for Previous Year

Re: Load tables from different Excel sheets with different sheet names?

Sun, 23 Feb 2014 03:45:51 GMT

use this in Edit Script , you may get the solution

For i = 0 To NoOfRow('Sheets')

Let zSheet = Peek('TABLE_NAME, i, 'Sheets');

... do something with the file and sheet name, eg

LOAD ....

From [$(vFile)]

(ooxml, no labels, table is [$(zSheet)]);