Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
Not applicable

Re: How can I convert columns into rows while loading the data

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
Not applicable

Re: How can I convert columns into rows while loading the data

Try The Crosstable Load:

CrossTable(City, Qty, 2)

LOAD Product,

          Name,

          Birmingham,

          London,

          Borth,

          Telford

FROM Source....

Not applicable

Re: How can I convert columns into rows while loading the data

Hi Nazir,

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

Thanks,

Vishnu.

Chanty4u
Not applicable

Re: How can I convert columns into rows while loading the data

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

Re: How can I convert columns into rows while loading the data

Check this out

Cross Table

Not applicable

Re: How can I convert columns into rows while loading the data

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);