I am new to Qlik and I would like to understand much better in creating right Cross table aggregation.
for example. In attached sheet for year 2015.
1. I would like to display the value of every vehicle type Old Registration with Months attributes.
2. I would like to display the value of every vehicle type New Registration with Months attributes.
May be this:
[Old Registration] & '|' &[New Registration],
[Old Registration1] & '|' &[New Registration1],
[Old Registration2] & '|' &[New Registration2],
[Old Registration3] & '|' &[New Registration3],
[Old Registration4] & '|' &[New Registration4],
[Old Registration5] & '|' &[New Registration5],
[Old Registration6] & '|' &[New Registration6],
[Old Registration7] & '|' &[New Registration7],
[Old Registration8] & '|' &[New Registration8],
[Old Registration9] & '|' &[New Registration9],
[Old Registration10] & '|' &[New Registration10],
[Old Registration11] & '|' &[New Registration11]
(biff, embedded labels, header is 1 lines, table is [46251-0005$]);
LOAD F1 as [Vehicle Type],
MakeDate(2015, RangeSum(1, KeepChar(SubField(Registration, '|', 1), '0123456789'))) as Date,
SubField(Data, '|', 1) as [Old Registration],
SubField(Data, '|', 2) as [New Registration]
Where Len(Trim(KeepChar(Data, '0123456789'))) > 0;
DROP Table Table;
This is more complicated script than a regular CrossTable Load, so would require some time posting a detailed response. I am home now, but will definitely post something detailed sometime tomorrow
okay is there a simple way, as you see in Excel.
1. There are Months ( Jan, Feb.....Dec)
2. for every month there are two categories ( Old Registration, New Registration)
3. Vehicle Type is linked to Month, Category, Value.
can we do something at transformation step and make changes when we apply crosstable and give name to attribuites and data?