Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there
I always struggle with crosstables etc. PLease help me with this tricky transpose of data.
I just need it to have the following columns :
Customer, Market Segment, Estimate Version, YEar, Month, Qty, TurnOver
(File is attached)
Please check this.
mapQty:
Mapping
LOAD Qty, Year&'@@'&Month INLINE [
Qty, Year, Month
Qty, 2014, Aug
Qty1, 2014, Dec
Qty2, 2014, Jul
Qty3, 2014, Jun
Qty4, 2014, Nov
Qty5, 2014, Oct
Qty6, 2014, Sep
];
mapTO:
Mapping
LOAD TO, Year&'@@'&Month INLINE [
TO, Year, Month
Turnover, 2014, Aug
Turnover1, 2014, Dec
Turnover2, 2014, Jul
Turnover3, 2014, Jun
Turnover4, 2014, Nov
Turnover5, 2014, Oct
Turnover6, 2014, Sep
];
tab1:
CrossTable (Key, Value, 3)
LOAD Customer,
[Market Sub Segment],
[Estimate Version],
Qty,
Turnover,
Qty1,
Turnover1,
Qty2,
Turnover2,
Qty3,
Turnover3,
Qty4,
Turnover4,
Qty5,
Turnover5,
Qty6,
Turnover6
FROM
[C:\Users\sarav\Downloads\Estimates.xls]
(biff, embedded labels, header is 2 lines, table is Sheet1$);
Left Join (tab1)
LOAD Customer
,[Market Sub Segment]
,[Estimate Version]
,SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key)),'@@',1) As Year
,SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key)),'@@',2) As Month
,If(Key Like 'Qty*', Value) As Qty
Resident tab1;
Left Join (tab1)
LOAD Customer
,[Market Sub Segment]
,[Estimate Version]
,SubField(If(Key Like 'Turnover*', ApplyMap('mapTO',Key)),'@@',1) As Year
,SubField(If(Key Like 'Turnover*', ApplyMap('mapTO',Key)),'@@',2) As Month
,If(Key Like 'Turnover*', Value) As Turnover
Resident tab1;
Are you looking something like this?
mapQty:
Mapping
LOAD Qty, Year&'@@'&Month INLINE [
Qty, Year, Month
Qty, 2014, Aug
Qty1, 2014, Dec
Qty2, 2014, Jul
Qty3, 2014, Jun
Qty4, 2014, Nov
Qty5, 2014, Oct
Qty6, 2014, Sep
];
mapTO:
Mapping
LOAD TO, Year&'@@'&Month INLINE [
TO, Year, Month
Turnover, 2014, Aug
Turnover1, 2014, Dec
Turnover2, 2014, Jul
Turnover3, 2014, Jun
Turnover4, 2014, Nov
Turnover5, 2014, Oct
Turnover6, 2014, Sep
];
tab1:
CrossTable (Key, Value, 3)
LOAD Customer,
[Market Sub Segment],
[Estimate Version],
Qty,
Turnover,
Qty1,
Turnover1,
Qty2,
Turnover2,
Qty3,
Turnover3,
Qty4,
Turnover4,
Qty5,
Turnover5,
Qty6,
Turnover6
FROM
[C:\Users\sarav\Downloads\Estimates.xls]
(biff, embedded labels, header is 2 lines, table is Sheet1$);
tab2:
LOAD *, SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key),ApplyMap('mapTO',Key)),'@@',1) As Year
, SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key),ApplyMap('mapTO',Key)),'@@',2) As Month
Resident tab1;
Drop Table tab1;
Drop Field Key, Value;
Please check this.
mapQty:
Mapping
LOAD Qty, Year&'@@'&Month INLINE [
Qty, Year, Month
Qty, 2014, Aug
Qty1, 2014, Dec
Qty2, 2014, Jul
Qty3, 2014, Jun
Qty4, 2014, Nov
Qty5, 2014, Oct
Qty6, 2014, Sep
];
mapTO:
Mapping
LOAD TO, Year&'@@'&Month INLINE [
TO, Year, Month
Turnover, 2014, Aug
Turnover1, 2014, Dec
Turnover2, 2014, Jul
Turnover3, 2014, Jun
Turnover4, 2014, Nov
Turnover5, 2014, Oct
Turnover6, 2014, Sep
];
tab1:
CrossTable (Key, Value, 3)
LOAD Customer,
[Market Sub Segment],
[Estimate Version],
Qty,
Turnover,
Qty1,
Turnover1,
Qty2,
Turnover2,
Qty3,
Turnover3,
Qty4,
Turnover4,
Qty5,
Turnover5,
Qty6,
Turnover6
FROM
[C:\Users\sarav\Downloads\Estimates.xls]
(biff, embedded labels, header is 2 lines, table is Sheet1$);
Left Join (tab1)
LOAD Customer
,[Market Sub Segment]
,[Estimate Version]
,SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key)),'@@',1) As Year
,SubField(If(Key Like 'Qty*', ApplyMap('mapQty',Key)),'@@',2) As Month
,If(Key Like 'Qty*', Value) As Qty
Resident tab1;
Left Join (tab1)
LOAD Customer
,[Market Sub Segment]
,[Estimate Version]
,SubField(If(Key Like 'Turnover*', ApplyMap('mapTO',Key)),'@@',1) As Year
,SubField(If(Key Like 'Turnover*', ApplyMap('mapTO',Key)),'@@',2) As Month
,If(Key Like 'Turnover*', Value) As Turnover
Resident tab1;