topic How to load a cross table with many headers? in QlikView

How to load a cross table with many headers?

swarnendu — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to load a cross table with many headers?

devarasu07 — Mon, 11 Dec 2017 10:58:26 GMT

Hi,

have you checked this article,

multi_header_pivot_import.qvw

Re: How to load a cross table with many headers?

swarnendu — Mon, 11 Dec 2017 11:26:47 GMT

Thanks for your reply but confuse in those script.

Re: How to load a cross table with many headers?

swarnendu — Wed, 13 Dec 2017 05:57:21 GMT

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.

Re: How to load a cross table with many headers?

tresB — Wed, 13 Dec 2017 06:23:26 GMT

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

Re: How to load a cross table with many headers?

swarnendu — Wed, 13 Dec 2017 06:35:02 GMT

Please Check this

Re: How to load a cross table with many headers?

tresB — Wed, 13 Dec 2017 06:43:03 GMT

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

Re: How to load a cross table with many headers?

swarnendu — Wed, 13 Dec 2017 09:30:36 GMT

Please check this

Re: How to load a cross table with many headers?

tresB — Wed, 13 Dec 2017 10:23:12 GMT

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.

Re: How to load a cross table with many headers?

swarnendu — Wed, 13 Dec 2017 11:54:44 GMT

Hi Tresesco,

Thanks For your reply.

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

Thanks in advance.