In the table shown , how to convert the column values (Mumbai,Chennai,Nagpur,Tamilnadu) into respective rows ?

[#1]

Thanks.

[sunny_talwar]

Read about CrossTable Load: The Crosstable Load

HTH

Best,

Sunny

[#3]

Try this

Tab1:

CrossTable(Place, [Hire (In figures)])

LOAD [Type of Source] as TYPE_OF_SRC,

     Mumbai,

     Chennai,

     Nagpur,

     TamilNadu

FROM

[QVC - 174139.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(

Remove(Row, Pos(Top, 2)),

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

Remove(Col, Pos(Top, 4)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 6)),

Remove(Row, Pos(Top, 16))

));

Tab2:

CrossTable(Place, [Hire (In %)])

LOAD [Type of Source] as TYPE_OF_SRC,

     Mumbai,

     Chennai,

     Nagpur,

     TamilNadu

FROM

[QVC - 174139.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1, filters(

Remove(Row, Pos(Top, 2)),

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

Replace(5, left, StrCnd(null)),

Replace(7, left, StrCnd(null)),

Replace(9, left, StrCnd(null)),

Remove(Col, Pos(Top, 8)),

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 4)),

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

Remove(Row, Pos(Top, 16))

));

Final:

LOAD

TYPE_OF_SRC as [Type of Source],

Place,

[Hire (In figures)]

Resident Tab1;

Join

LOAD

TYPE_OF_SRC as [Type of Source],

Place,

[Hire (In %)]

Resident Tab2;

DROP Tables Tab1,Tab2;

Regards,

KKR

[MarcoWedel]

Hi,

maybe this helps:

How to load a crosstable with two header rows/2 attribute fields.

Re: Re: Extracting multiple headers from Crosstable

Re: Cross table

multi_header_pivot_import.qvw

regards

Marco