topic Re: Transpose the data in Qlik Sense in App Development

Transpose the data in Qlik Sense

kartik — Fri, 15 Nov 2024 20:55:26 GMT

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

Re: Transpose the data in Qlik Sense

marksouzacosta — Sat, 06 Jul 2024 17:04:37 GMT

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

Re: Transpose the data in Qlik Sense

kartik — Sun, 07 Jul 2024 05:19:45 GMT

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 ?

Re: Transpose the data in Qlik Sense

marksouzacosta — Sun, 07 Jul 2024 05:38:01 GMT

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

Re: Transpose the data in Qlik Sense

rubenmarin — Sun, 07 Jul 2024 10:12:51 GMT

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() ));

Re: Transpose the data in Qlik Sense

TauseefKhan — Mon, 08 Jul 2024 12:08:39 GMT

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.***