I have a cross table which has many headers rows. I now couldn't understand How to load that into qlikview & make it a straight table format. Please suggest.
Thanks in advance.
Thanks for your reply its worked when i take only 1 sheet...
Could you share the qvw that 'worked' for you? (so that we we can start from there without having to re-work).
Try creating ODBC connection to your excel and then you can use SQLTables to build a list of sheets in your file, then loop through that. Try something like:
ODBC CONNECT TO [Excel Files;DBQ=Your Excel File.xlsx];
//use SQLTables to assemble a list of all the sheets in this Excel file
DISCONNECT; //from excel
for n = 0 to NoOfRows('XLSSheetList')
LET vXLSXSheet = peek('TABLE_NAME', n, 'XLSSheetList');
//Concatenate statement here
rowno()+$(vVDims) as ColNo
($(vType), no labels, table is $(vXLSXSheet), filters(
// Load the pivot table with the Crosstable function
LET vHeaders = $(vHDims)-1;
//Concatenate statement here too, may be
load * from $(vSourceFile)
($(vType), embedded labels, header is $(vHeaders) lines, table is $(vXLSXSheet));
Note: This is a rough idea. Few things you have to deal with additionally in real scenario, like concatenating the table in the loop. I could not test it without the multi-sheet data and for lack of enough time. Hope you get the idea and move forward.