Help to parse an excel

[#1]

Hello!

Could someone advice a way to parse such an excel without modifying excel / or minimally modifying it (in example onle 3 months? actually all year is presented)?

Thanks in advance!

[#2]

Hi!

Month names (row 1) can be advanced to right or year labels (row 4) can be advanced to left. So, for each columns we will have a month and a year. The second complexity - both horizontal and vertical headers have more than one dimensions. So, for crosstable operation one of headers have to be joined temporary to one dimension. It need to intermediate store and two transformations. Like that:

T1:

NoConcatenate LOAD

  If(RecNo() <= 4, 'F' & RecNo(), @1 & '|' & @2) as 12,

  *

From [example.xlsx] (ooxml, no labels,

filters(

  Transpose(),

  Replace(1, top, StrCnd(null)),

  Replace(4, bottom, StrCnd(contain, ' 2', not)),

  Transpose(),

  Remove(Row, Pos(Top, 3)),

  Remove(Row, Pos(Top, 1))

  ,Transpose()

));

STORE T1 into Tmp.qvd (qvd);

DROP Table T1;

T2:

CrossTable(Combo, Value, 4)

LOAD * From [Tmp.qvd] (qvd, embedded labels,

filters(

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

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

  Transpose()

));

T3:

NoConcatenate LOAD

  F1 as Manager,

  F2 as Product,

  F3 as State,

  F4 as Client,

  SubField(Combo, '|', 1) as Type,

  SubField(SubField(Combo, '|', 2), ' ', 3) as Year,

  SubField(SubField(Combo, '|', 2), ' ', 2) as Month,

  Value

Resident T2

Where Len(F4) > 0 and Len(SubField(Combo, '|', 1)) > 0;

DROP Table T2;