topic Re: Reading from multiple worksheets using Qlikviews in QlikView

Reading from multiple worksheets using Qlikviews

Mon, 04 Jul 2011 07:32:16 GMT

SET FilepathName = <input file path>;

CONNECT TO [Excel Files;DBQ=$(FilepathName)];

tables: // Name the table that will be created by SQLTables stmt
SQLtables;
DISCONNECT; // Don't need ODBC connection anymore

FOR SheetCounter = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', SheetCounter, 'tables'), chr(39));
DataTable:
LOAD col1,col2,col3 , '$(sheetName)' as sheet

FROM $(FilepathName) (biff, embedded labels, table is [$(sheetName)]);

NEXT
DROP Table tables;
Let rowlimit = NoOfRows(DataTable);

Table2:
Load col2,col3 from DataTable;

Getting following error message -

Cannot open file '<QVworking folder>\DataTable', The operation completed successfully.

Table2:
Load col2,col3 from DataTable;

Please help .

Re: Reading from multiple worksheets using Qlikviews

Miguel_Angel_Baeyens — Mon, 04 Jul 2011 08:18:58 GMT

Hi,

At a first glance, I don't know if that's a typo, but check

Let rowlimit = NoOfRows('DataTable'); // table needs to be single quoted

EDIT: Apart from that, you need to specify RESIDENT instead of FROM when loading from DataTable:

Table2:

Load col2,col3

RESIDENT DataTable;

FROM keyword is intended to sources external to qlikview. RESIDENT means loading from an already previously loaded table, that is the case.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Reading from multiple worksheets using Qlikviews

Mon, 04 Jul 2011 09:51:24 GMT

It was really helpful. Because of From keyword it was searching files external to QV.