Transpose rowwise data into columnwise data

[HWallays]

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

 To

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 ?

[alex00321 · Accepted solution]

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;

[Brett_Bleess]

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

[HWallays]

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. 

[HWallays]

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.

[alex00321]

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!