topic How load only last sheet from file? in QlikView

How load only last sheet from file?

vchuprina — Sun, 07 Jun 2015 15:21:47 GMT

Hi All,

Every week I'm receiving files with multiple sheets, but I need to load data only from the last one.

Problem that each sheets has own name and these names are different from week to week, for example:

How I can load data only from the last sheet?

Re: How load only last sheet from file?

marcus_sommer — Sun, 07 Jun 2015 17:00:48 GMT

See here an example how do you could load multiple sheets.

https://community.qlik.com/docs/DOC-7860

I'm not sure if it's possible to recognize within the sheet-table the last or newest sheet but if there isn't an unique indicator then you could store the already loaded sheetnames and check these within an if-loop if they match with the available sheets or not.

- Marcus

Re: How load only last sheet from file?

Anonymous — Wed, 19 Aug 2015 15:35:17 GMT

Just putting here the code to do so using part of what Marcus shared

set file = '[PATH]\[FILE].xlsx';

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

Last:

first 1

load

TABLE_NAME

resident tables

order by TABLE_NAME DESC;

let LastTab = replace(Peek('TABLE_NAME',0,'Last'),'$','');

drop Tables tables,Last;

LOAD *

FROM

[[PATH]\[FILE].xlsx]

(ooxml, embedded labels, table is $(LastTab));