Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reading from multiple worksheets using Qlikviews


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 .

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

2 Replies
Miguel_Angel_Baeyens

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

Not applicable
Author

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