Help to parse an excel

Sun, 18 Jan 2015 17:47:08 GMT

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!

Re: Help to parse an excel

Sun, 18 Jan 2015 21:33:18 GMT

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;

topic Re: Help to parse an excel in QlikView

Help to parse an excel

Re: Help to parse an excel