Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How can I convert columns into rows while loading the data

 

Hi Experts,

 

I have an excel file like this 

 

 

Product

Name

Birmingham

London

Borth

Telford

C0001

Chemical 01

100

90

20

120

C0002

Chemical 02

25

75

25

80

 

 

I want it in this way:

 

Product

Name

City

Qty

C0001

Chemical 01

Birmingham

100

C0001

Chemical 01

London

90

C0001

Chemical 01

Borth

20

C0001

Chemical 01

Telford

120

C0002

Chemical 02

Birmingham

25

C0002

Chemical 02

London

75

C0002

Chemical 02

Borth

25

C0002

Chemical 02

Telford

80

 

Can somebody please guide!

6 Replies
Gysbert_Wassenaar

You can use a CrossTable load:

CrossTable(City, Qty,2)

LOAD * FROM excelfile.xlsx (ooxml, embedded labels, table is Sheet1);


talk is cheap, supply exceeds demand
sunny_talwar

Try The Crosstable Load:

CrossTable(City, Qty, 2)

LOAD Product,

          Name,

          Birmingham,

          London,

          Borth,

          Telford

FROM Source....

Not applicable
Author

Hi Nazir,

By using cross table, u can able to converts columns into rows...

Thanks,

Vishnu.

Chanty4u
MVP
MVP

try this

CrossTable(Nam, qty, 2)

LOAD Product,

     Name,

     Birmingham,

     London,

     Borth,

     Telford

FROM

[https://community.qlik.com/thread/231209?sr=inbox&ru=225238]

(html, codepage is 1252, embedded labels, table is @1);

Result

crs.PNG

Not applicable
Author

Check this out

Cross Table

Not applicable
Author

Hi Shazad,

     While uploading data from excel into qlikview, Select Table Files --> use Crosstable... option in File wizard. In that set Qualifier field as 2, Change the name for Attribute Field as 'City' and also change the name for Data Field as 'Qty'. Then click OK and Finish.

It should be like below

Sample:

CrossTable(City, Qty, 2)

LOAD Product,

     Name,

     Birmingham,

     London,

     Borth,

     Telford

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet1);