Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jonathanpoyer
Contributor

Load an Excel Table (ListObject)

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.

Tags (2)
7 Replies

Re: Load an Excel Table (ListObject)

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

- Marcus

jonathanpoyer
Contributor

Re: Load an Excel Table (ListObject)

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)

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,

     B,

     C,

     D,

     E,

     F

FROM

(ooxml, no labels, table is Sheet1);

- Marcus

jonathanpoyer
Contributor

Re: Load an Excel Table (ListObject)

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)

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

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

See also: Offset Function in Excel - Easy Excel Tutorial

- Marcus

jonathanpoyer
Contributor

Re: Load an Excel Table (ListObject)

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)

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

Community Browser