topic Re: Converting rows into multiple columns in QlikView

Converting rows into multiple columns

farheenayesha — Tue, 08 Jan 2013 07:19:16 GMT

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.

Re: Converting rows into multiple columns

khadeer — Tue, 08 Jan 2013 07:27:32 GMT

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

use these transformations ur prblm will resolve ...

Re: Converting rows into multiple columns

khadeer — Tue, 08 Jan 2013 07:32:55 GMT

Find the attachments.

Re: Converting rows into multiple columns

farheenayesha — Tue, 08 Jan 2013 07:34:27 GMT

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

Re: Converting rows into multiple columns

khadeer — Tue, 08 Jan 2013 07:37:37 GMT

Attach a sample excel sheet.

Re: Converting rows into multiple columns

farheenayesha — Tue, 08 Jan 2013 07:42:14 GMT

Please find the sample file

Re: Converting rows into multiple columns

vikasgupta — Tue, 08 Jan 2013 08:10:02 GMT

Plz follow the steps during table file load

Transformation

Rotate

Transpose

Thanks

Vikas

Re: Converting rows into multiple columns

sujeetsingh — Tue, 08 Jan 2013 08:54:57 GMT

try pivot and turn the horizontal view on presentation

Re: Converting rows into multiple columns

Tue, 08 Jan 2013 09:12:54 GMT

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.

Re: Converting rows into multiple columns

farheenayesha — Tue, 08 Jan 2013 09:37:55 GMT

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?

Re: Converting rows into multiple columns

flipside — Tue, 08 Jan 2013 09:38:28 GMT

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

Re: Converting rows into multiple columns

farheenayesha — Tue, 08 Jan 2013 09:52:02 GMT

Thank you so much...this is exactly what i wanted!!

Re: Converting rows into multiple columns

Tue, 08 Jan 2013 21:50:49 GMT

Afreen,

Assuming your data has similar format as the sample, you may split the table and join it by the rowno() function as Flipside suggested.

This will work with any number of rows.

You must however, ensure, that your code does not form synthetic keys (look for it in Help or Reference Manual, which could easily run you out of memory, especially for large datasets.

Regards,

-Khaled.

Re: Converting rows into multiple columns

farheenayesha — Wed, 09 Jan 2013 03:31:27 GMT

Thanks Khaled..

I'l make sure that my code does not form any synthetic keys.

Re: Converting rows into multiple columns

jeroen_wagner — Wed, 28 Oct 2015 10:34:01 GMT

I want just this, only in a loop creating the Columns dynamicly, as the amount of rows>>columns can change in time.

Any ideas?

So really some kind of loop, depending on the number of items, generating the columns needed