topic How to load different tables from a single excel sheet into seperate tables? in QlikView

How to load different tables from a single excel sheet into seperate tables?

Fri, 01 Mar 2013 08:12:00 GMT

Hi,

I have one excel sheet contains different sheets, but one sheet have more than one table.

QV is loading those tables as one table. Could you please suggest us how to load those tables seperately by recognising their headers?

Thanks in advance,

Kumari.

Re: How to load different tables from a single excel sheet into seperate tables?

hic — Fri, 01 Mar 2013 08:59:35 GMT

You need to create seveal load statements in the QlikView script, and make sure that each Load contains just the fields you want to have.

HIC

Re: How to load different tables from a single excel sheet into seperate tables?

Fri, 01 Mar 2013 09:29:49 GMT

Hi Henric,

Thanks for your reply, those two tables have the same field names and same no.of fields, but the data varies.

Please help me on this...

Kumari

Re: How to load different tables from a single excel sheet into seperate tables?

hic — Fri, 01 Mar 2013 09:39:30 GMT

Let me get this straight: You have one sheet in an Excel document that has two tables, and the two tables have identical sets of fields? I would change this Excel sheet so that it gets one table per sheet.

But if you cannot do that, you could instead load the two tables using columnar addresses instead. Choose "None" inthe labels control and adjust the number of header lines so that you do not load the field names.

Then you will get a Load statement that loads @1, @2, etc. Rename these to the proper field names.

LOAD @1 as FieldA,

@2 as FieldB,

@3 as FieldC

FROM

Then do the same for the second table.

HIC

Re: How to load different tables from a single excel sheet into seperate tables?

Fri, 01 Mar 2013 10:27:38 GMT

Henric, my tables are below one another means have the same column number shown in the attached excel sheet, please suggest me accordingly.

Kumari.

Re: How to load different tables from a single excel sheet into seperate tables?

hic — Fri, 01 Mar 2013 11:00:24 GMT

Since the bottom two two tables are summations of the top table, I would just load the raw data using a where clause. I would load no subtotals whatsoever. It's better to create the totals and the subtotals in QlikView.

For example

LOAD [Month / Year],

Project,

Service,

[Head Count]

FROM [Book1 (2).xlsx] (ooxml, embedded labels, table is Sheet1)

Where IsNum([Month / Year]) and RecNo() <= 13;

HIC