Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

stijnvalkenburg
New Contributor

Changing rows to columns

Hi all,

I have like 20 seperate excels with below format. Is there an easy way to change the rows to columns?

  

FieldValue
ScoreA1
ScoreB3
ScoreC5

So I want it in Qlik Sense like this:

ScoreAScoreBScoreC
135

Thanks!

1 Solution

Accepted Solutions
thiago_justen
Valued Contributor III

Re: Changing rows to columns

I guess what stalwar1‌‌ and omarbensalem‌‌  said is more easier and will provide you the same result of this trick here:

Table:

Load * Inline [

Field,Value

ScoreA,1

ScoreB,3

ScoreC,5

];

Generic

LOAD 1 as ID,

  Field,

  Value

Resident Table;

Drop Fields Field,Value,ID;

But, it is just another way to do this (Imagining that you don't want to use a pivot table, for instance).

Result using a simple table:

Capturar.PNG

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
13 Replies
thiago_justen
Valued Contributor III

Re: Changing rows to columns

You can use Generic Load for this purpose.

The Generic Load

Another way to do this is:

Re: Any alternate to Generic load?

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago

Re: Changing rows to columns

Why can't you just use a pivot table to display the information like that?

stijnvalkenburg
New Contributor

Re: Changing rows to columns

I get them delivered like this and don't want to change every file I receive

stijnvalkenburg
New Contributor

Re: Changing rows to columns

Looks like a good idea but I don't think I have a Key to put in the script..

OmarBenSalem
Esteemed Contributor

Re: Changing rows to columns

what sunny meant, is there is no need to alter ur script;

just in ur sheet, use a pivot table

as column: Field

as a measure: sum(Score)

and u'll have the view u're looking for WITHOUT CHANGING UR SCRIPT:

Capture.PNG

stijnvalkenburg
New Contributor

Re: Changing rows to columns

Yes that would be an option indeed. Except I want to concatenate all files into one table and add an extra column based on the filename. Additionally the rows differ in values (text and numerical values) so unfortunately it isn't as simple as the example I've displayed..

thiago_justen
Valued Contributor III

Re: Changing rows to columns

I guess what stalwar1‌‌ and omarbensalem‌‌  said is more easier and will provide you the same result of this trick here:

Table:

Load * Inline [

Field,Value

ScoreA,1

ScoreB,3

ScoreC,5

];

Generic

LOAD 1 as ID,

  Field,

  Value

Resident Table;

Drop Fields Field,Value,ID;

But, it is just another way to do this (Imagining that you don't want to use a pivot table, for instance).

Result using a simple table:

Capturar.PNG

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago

Re: Changing rows to columns

Might help if you share a sample from 2-3 files to show what you have

stijnvalkenburg
New Contributor

Re: Changing rows to columns

This is an example, all files look the same with the same fields.

Community Browser