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: 
farheenayesha
Creator
Creator

Converting rows into multiple columns

I have a question as below,

untitled.bmp

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

untitled1.bmp

please someone help me to get the solution.

Thanks in advance.

1 Solution

Accepted Solutions
flipside
Partner - Specialist II
Partner - Specialist II

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

14 Replies
khadeer
Specialist
Specialist

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

use these transformations ur prblm will resolve ...

khadeer
Specialist
Specialist

Find the attachments.

farheenayesha
Creator
Creator
Author

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 Idfirst namelast nameagegenderphoneCust Idfirst namelast nameagegenderphone

I want the output lik this

untitled1.bmp

khadeer
Specialist
Specialist

Attach a sample excel sheet.

farheenayesha
Creator
Creator
Author

Please find the sample file

vikasgupta
Creator
Creator

Hi

Plz follow the steps during table file load

Transformation

Rotate

Transpose

Thanks

Vikas

sujeetsingh
Master III
Master III

try pivot and turn the horizontal view on presentation

Not applicable

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
Creator
Creator
Author

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?