Excel Data Reading

[tresesco]

Hello,

i have an excel of the follwoing format:

Date	Department A	Date	Department B	Date	Department C
1-1-13	100	1-1-13	111	2-2-12	200
2-1-13	110	3-1-13	112	12-1-13	500

Now i need to read this into qlikview to get out put as below:

Date	Department	Amount
1-1-13	A	100
2-1-13	A	110
1-1-13	B	111
3-1-13	B	112
2-2-12	C	200
12-1-13	C	500

How do i do that?

Thanks.

[sushil353]

Hi Kumar,

I don't have any tutorial for Enable Transformation Step..

Just got idea by experience only..

Regards,

Sushil

[tresesco]

it seems that your latest app uses different excel.

right(@3,1) as Department??

Thanks.

[sushil353 · Accepted solution]

Put the below code in the QV file..

t1:

LOAD Date(@1,'MM-DD-YYYY') as Date,

     num(@2) as Amount,

     right(@3,1) as Department

FROM

tableformat.xlsx

(ooxml, explicit labels, table is Sheet1, filters(

Unwrap(Col, Pos(Top, 3)),

Unwrap(Col, Pos(Top, 3)),

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null)), 0),

Replace(3, left, StrCnd(null)),

Replace(3, top, StrCnd(start, 'D', not)),

Remove(Row, RowCnd(Compound,

          RowCnd(CellValue, 1, StrCnd(equal, 'Date')),

          RowCnd(Interval, Pos(Top, 2), Pos(Top, 1), Select(1, 0))

)),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Date')))

));

Regards,

Sushil

[tresesco]

thanks Sushil for your effort and help. now i have to explore with the transformation how you achieved the same.

[sushil353]

if need any help then keep posting..

[kumarnatarajan]

Hi,

try this one. This is dynamic.

T1:
CrossTable(particular,Data,2)
LOAD RowNo() as No,
  @1&'-'&Previous(@1) as new,
     @2,
     @3
FROM
Data2.xlsx
(ooxml, explicit labels, table is Sheet1, filters(
Transpose(),
Transpose(),
Transpose(),
Rotate(right),
Transpose()
));

MapTab:
Mapping LOAD particular&'-'&new as map,Data as Datamap Resident T1;

NoConcatenate LOAD SubField(new,'-',1) as Department,Data as Amount,
Date(ApplyMap('MapTab',particular&'-Date-'&SubField(new,'-',1),0)) as Date
Resident T1 Where ApplyMap('MapTab',particular&'-Date-'&SubField(new,'-',1),0)>0;
DROP Table T1;

find the attached file.