Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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;
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;
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
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.
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.
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!