Transpose the data in Qlik Sense

[kartik]

Hi Guys,

I have a good question to ask.

We have data given below in Excel in the particular format. What I need to do is, need to make the rows as a column in the data load editor.

Data:

Code	1	2	3	4
Colour	Red	Green	Yellow	Blue
Country	DE	UK	FR	DK

Using the above data I need to transform the data the same as the output table.

Output:

Code	Colour	Country
1	Red	DE
2	Green	UK
3	Yellow	FR
4	Blue	DK

[marksouzacosta]

Hi @kartik,

I hope there is a better way, but this is how I did it:

// Start the final table
[Final]:
LOAD * INLINE [
Key
1
2
3
4
];

For Each vType In 'Code','Colour','Country'

[Temp$(vType)]:
Crosstable ([T$(vType)], [$(vType)])
LOAD
 [C],
 [D],
 [E],
 [F],
 [G]
FROM
 [lib://DataFiles/Cross_Table.xlsx]
 (ooxml, no labels, table is Tabelle1)
WHERE
   Match([C],'$(vType)')
;

LEFT JOIN([Final]) LOAD RecNo() AS [Key], [$(vType)] RESIDENT [Temp$(vType)];

DROP TABLE [Temp$(vType)];

Next vType

DROP FIELD [Key];

Regards,

Mark Costa

[kartik]

Hi @marksouzacosta , 

Thanks, this worked but I think there should be some easy way too for this....

Can't we do this with Crosstable or generic load ?

[marksouzacosta]

Crosstable and Generic Loads were my first attempts but I had no success with them. I don't discard the possibilities of better and more straightforward solutions. I'm just not seeing it yet.

Regards,

Mark Costa

[rubenmarin]

Hi, if excel is the source you can add tranformations while loading the data, like:

LOAD Code, 
     Colour, 
     Country
FROM
[lib://DataFiles/Cross_Table.xlsx]
(ooxml, embedded labels, table is Tabelle1, filters(
	Remove(Row, RowCnd(CellValue, 1, StrCnd(shorter, 0))),
	Remove(Col, Pos(Top, 1)),
	Transpose()
));

[TauseefKhan]

Hi @kartik.,

Check this one:

[Your_CrosstableTable]:
Crosstable (Code, Value, 1)
LOAD * INLINE [
Attribute, 1, 2, 3, 4
Colour, Red, Green, Yellow, Blue
Country, DE, UK, FR, DK
];

FinalTable:
LOAD
Code as Code,
If(Attribute = 'Colour', Value) as Colour
Resident [Your_CrosstableTable]
Where Attribute = 'Colour' ;

Left JOIN (FinalTable)
LOAD
Code as Code,
If(Attribute = 'Country', Value) as Country
Resident [Your_CrosstableTable]
Where Attribute = 'Country';

DROP TABLE [Your_CrosstableTable];

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***