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;
