How to load a cross table with many headers?

[swarnendu]

Hi all,

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.

.

[devarasu07]

Hi,

have you checked this article,

multi_header_pivot_import.qvw

[swarnendu]

Thanks for your reply but confuse in those script.

[swarnendu]

Hi Devarasu,

Thanks for your reply its worked when i take only 1 sheet.

But i have 30 sheets so how its work can you please explain.

[tresesco]

Try to share your 'working' solution (with single sheet). It would be easier for us to help you onward.

[swarnendu]

Please Check this

[tresesco]

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).

[swarnendu]

Please check this

[tresesco]

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

XLSSheetList:

SQLTables

;

DISCONNECT; //from excel

........

for n = 0 to NoOfRows('XLSSheetList')

LET vXLSXSheet = peek('TABLE_NAME', n, 'XLSSheetList');

Levels:

//Concatenate statement here

LOAD $(vHFieldList),

     rowno()+$(vVDims) as ColNo

FROM $(vSourceFile)

($(vType), no labels, table is $(vXLSXSheet), filters(

Transpose(),

$(vRemoveRows),

$(vReplaces)

));

//  Load the pivot table with the Crosstable function

LET vHeaders = $(vHDims)-1;

CT:

//Concatenate statement here too, may be

CrossTable(ValCol, Value1,$(vVDims))

load * from $(vSourceFile)

($(vType), embedded labels, header is $(vHeaders) lines, table is $(vXLSXSheet));

next n

.....

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.

[swarnendu]

Hi Tresesco,

Thanks For your reply.

Can you provide me some example or example  file   like this.

Thanks in advance.