Loop through Excel sheet +crosstable

Tue, 14 Oct 2014 09:25:43 GMT

Hello,

I have to load a file Excel having many sheet, each sheet contains the same data.

I made some transformation to my data:

DataStock:

CrossTable(Items , DataStock)

LOAD F1 as DateStock,

....

FROM

[file.xlsx];

(ooxml, embedded labels, table is [SEM 20], filters(

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 1)),

Remove(Row, Pos(Top, 49)),

Remove(Row, Pos(Top, 48)),

Transpose(),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 21)),

Transpose()

));

DataArrivee:

CrossTable(Items, DataArrivee)

LOAD

date(F1) as DateArrivee,

Adhérents,

...

FROM

[file.xlsx]

(ooxml, embedded labels, table is [SEM 20], filters(

Remove(Col, Pos(Top, 24)),

Remove(Col, Pos(Top, 23)),

Remove(Row, Pos(Top, 16)),

Remove(Row, Pos(Top, 15)),

Transpose()

));

Join (DataTable)

LOAD

* Resident DataArrivee;

DROP Table DataArrivee;

But I want to do this for all the sheets, I have to apply a loop but I can't found how!

I need your help please.

Thank you

topic Loop through Excel sheet +crosstable in QlikView