Solved: Re: Converting rows into multiple columns - Qlik Community

farheenayesha · ‎2013-01-08

I have a question as below,

i want to convert the above rows into columns as in below diagram.

please someone help me to get the solution.

Thanks in advance.

flipside · ‎2013-01-08

If I'm right in assuming you will have a long list of records (6 rows for each record), then an approach like the following will achieve what you want ...

Data:

LOAD rowno() as link, B as [Cust Id]

FROM Sample.xlsx (ooxml, no labels, table is Sheet1)

where A = 'Cust Id';

left join LOAD rowno() as link, B as [first name]

FROM Sample.xlsx (ooxml, no labels, table is Sheet1)

where A = 'first name';

left join LOAD rowno() as link, B as [last name]

FROM Sample.xlsx (ooxml, no labels, table is Sheet1)

where A = 'last name';

.. + 3 more for age, gender, phone

This will only work if you can guarantee 6 pieces of data for each record AND all data labels are consistently named otherwise the link will corrupt.

flipside

View solution in original post

khadeer · ‎2013-01-08

Load the excel Select-----> Next------> enable transformation step----->Rotate tab

use these transformations ur prblm will resolve ...

khadeer · ‎2013-01-08

Find the attachments.

farheenayesha · ‎2013-01-08

Thanks for the response .I did used the rotate option but it is not coming as i want. first 6 columns are repeating as below

Cust Id

first name

last name

age

gender

phone

Cust Id

first name

last name

age

gender

phone

I want the output lik this

khadeer · ‎2013-01-08

Attach a sample excel sheet.

farheenayesha · ‎2013-01-08

Please find the sample file

vikasgupta · ‎2013-01-08

Hi

Plz follow the steps during table file load

Transformation

Rotate

Transpose

Thanks

Vikas

sujeetsingh · ‎2013-01-08

try pivot and turn the horizontal view on presentation

Report Inappropriate Content · ‎2013-01-08

Afreen,

Apparently, you have columns with similar (same!) names in the Excel file.

QlikView will treat these as seperate columns by default on a regular load.

To get the desired result, you can Extract - Transform and Load the source (Excel) file.

Since your data has an ID which is seemingly a primary key, splitting the table load and joining them again on this field must give you the desired result

Please find attachment for the same.

Hope that helps.

Regards,

-Khaled.

farheenayesha · ‎2013-01-08

Thank you every one..

Thanks Khalid..Your ans is very helpful for me but here i have only 2 records but how shud i implement this on 1000's of records?