CrossTable/Transpose/Rotate

[BradleyIsaacs]

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)

[Saravanan_Desingh]

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;

[Saravanan_Desingh · Accepted solution]

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;