Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have like 20 seperate excels with below format. Is there an easy way to change the rows to columns?
Field | Value |
---|---|
ScoreA | 1 |
ScoreB | 3 |
ScoreC | 5 |
So I want it in Qlik Sense like this:
ScoreA | ScoreB | ScoreC |
---|---|---|
1 | 3 | 5 |
Thanks!
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:
You can use Generic Load for this purpose.
Another way to do this is:
Re: Any alternate to Generic load?
Why can't you just use a pivot table to display the information like that?
I get them delivered like this and don't want to change every file I receive
Looks like a good idea but I don't think I have a Key to put in the script..
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:
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..
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:
Might help if you share a sample from 2-3 files to show what you have
This is an example, all files look the same with the same fields.