topic Re: Load an Excel Table (ListObject) in QlikView

Load an Excel Table (ListObject)

Anonymous — Fri, 03 Jun 2016 06:54:34 GMT

Dear community,

Loading Excel is so much fun. Though since 2003 we began to see a new type of object called ListObject. This object is quite similar to a named range but is in fact publicly named a Table. Since then many users are using this object. I can't find another parameter in the statement for file definition.

While loading Named range is totally fine, loading a Table (or ListObject in VBA) seems impossible. Have you encounter this type of issue. How did you deal with that?

Much thanks in advance.

Re: Load an Excel Table (ListObject)

marcus_sommer — Fri, 03 Jun 2016 11:06:16 GMT

If you could elaborate it in more detail and provide a small example it will be helpful.

- Marcus

Re: Load an Excel Table (ListObject)

Anonymous — Fri, 03 Jun 2016 11:46:37 GMT

Hi Marcus,

Here is an Excel File.

You will find:

a Named Table (Table1)
a Named Range (Range1)

The Named Table (Table1) is not available as an option to be loaded in QlikView whereas the Named Range (Range1) is.

Thanks,

Re: Load an Excel Table (ListObject)

marcus_sommer — Fri, 03 Jun 2016 12:45:17 GMT

I think that these Table (ListObject) is no own data-area it's a feature (on a higher level) which will be applied to a data-area and therefore you couldn't access these virtual object directly.

But if you give these Table an additional name you could access it and a normal odbc-load on the sheet or a normale table-load will be work also:

ODBC CONNECT32 TO [DriverForExcel;DBQ=I:\MyPath\TestLoadListObjects.xlsx];

SQL SELECT Country,

Sales

FROM `I:\MyPath\TestLoadListObjects.xlsx`.`Sheet1$`;

SQL SELECT *

FROM `I:\MyPath\TestLoadListObjects2.xlsx`.NameForTable; // with a name for this ListObject

Directory;

LOAD A,

FROM

(ooxml, no labels, table is Sheet1);

- Marcus

Re: Load an Excel Table (ListObject)

Anonymous — Fri, 03 Jun 2016 14:50:32 GMT

Hi Markus,

Thank you for your reply.

Unfortunately, here is a very quick example and the users will have to manage multiple Named Range to apply on top of the Named Table which is not helpful as the table can extend automatically without the range be extended.

I guess you confirmed I have to find another option to deal with that.

Thank you!

Re: Load an Excel Table (ListObject)

marcus_sommer — Fri, 03 Jun 2016 18:49:11 GMT

The excel name-ranges could be dynamically with an expression like this:

offset(Sheet1!$A$1;;;CountA(Sheet1!$A:$A);CountA(Sheet1!$1:$1))

- Marcus

Re: Load an Excel Table (ListObject)

Anonymous — Mon, 06 Jun 2016 06:25:02 GMT

Hi Markus,

I didn't know this function thanks. After exploring it, I can say it won't work. Thank you thou on that matter.

Best,

Re: Load an Excel Table (ListObject)

marcus_sommer — Mon, 06 Jun 2016 07:19:22 GMT

For me these methods worked very well since years - I use it for all my dynamic data-areas, mostly for pivot-tables. Therefore have a further look to the idea - maybe I have made an error by choosing/translating the right function/syntax - in german in function will be called as Bereich.Verschieben().

- Marcus