topic Re: Transpose rowwise data into columnwise data in QlikView

Transpose rowwise data into columnwise data

HWallays — Sat, 16 Nov 2024 19:24:03 GMT

Is there an easy way to transpose data (during import or from resident tables ) from rowwise orientation into columnwise orientation ? I mean going from input like below

Dimensions in the table : Agency, Year, Revenue

data line 1 : Ag1 , 1980, 10000

date line 2 : Ag1, 1981, 12000

Dimensions in the table : Agency, Revenue1980, Revenu1981

data line : Ag, 10000, 12000

Reason why I want this is because I need to calculate delta's over the years and with rowwise that's not an easy option if you have quite a number of years to cover … 😉

I know the opposite is possible (from columns to rows) but is the inverse also possible ?

Any suggestions/possibilities ?

Re: Transpose rowwise data into columnwise data

alex00321 — Tue, 24 Dec 2019 08:54:09 GMT

Try to solve your question in Script:

tblTest:
LOAD * Inline[
Agency, Year, Revenue

Ag1, 1980, 10000
Ag2, 1981, 12000
Ag3, 1982, 13000
Ag4, 1983, 14000
];

tblFinal:
LOAD distinct Agency
Resident tblTest;

FOR i=1 to FieldValueCount('Year')
LET vField=FieldValue('Year',$(i));

Left Join(tblFinal)
LOAD Agency, Revenue as [Revenue $(vField)]
Resident tblTest Where Year=$(vField);
NEXT i;

DROP Table tblTest;

Re: Transpose rowwise data into columnwise data

Brett_Bleess — Thu, 26 Dec 2019 16:33:23 GMT

Check out the following Design Blog link:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

Here is the base URL in case you want to do further searching on your own:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Re: Transpose rowwise data into columnwise data

HWallays — Mon, 30 Dec 2019 07:43:07 GMT

Hai BrettBlees, thanks for your link. What I want / need to do is actually the inverse of the crosstable function 😉

But I will keep the crosstable function in mind too for other things I have in my mind to setup . It seems quite handy indeed.

Re: Transpose rowwise data into columnwise data

HWallays — Mon, 30 Dec 2019 07:49:26 GMT

Hai Alex00321 , have tried this out and it does work, bumped into another problem that currently stops me from using it , but this is an issue with the data rather then this solution : finding the unqiue key (concatenation of various dimensions) field in order to prevent unwanted duplicates , but that's to be fixed at the data source first. So I think I will be able to proceed with this . Thanks for the fine support.

PS. For a novice like me it is not that easy readable, but have been playing with some parameters and understand what it does.

Re: Transpose rowwise data into columnwise data

alex00321 — Mon, 30 Dec 2019 09:02:43 GMT

Hi to solve the problem, I also try to use Python and VBA to deal with data firstly. I think you could separate the process which is dealing with data firstly with the tool you are familiar with, then handle that in Qlik.

Thanks!