Want to reduce total number of columns

[Anonymous]

Hi, I have a data set which has columns like:

Country	Org	Sale0	sale1	Sale2	inhand0	inhand1	Inhand2	Inventory0	Inventory1	Inventory1
Ind	XYZ	10	12	33	15	20	70	1000	800	1200
Aus	abc	100	120	50	30	45	65	500	700	600

The problem I am facing is that when I use pivot table selecting Country ,Org as dims and write expressions to get sale0,sale1,sale2,inhand0,inhand1,inhand2,inventory0,inventory1,inventory2, it keeps on adding the columns. I have around 12 metrics running across 13 quarters which mean I have 12*13=156 columns. Basically I want to reduce the no of columns to 14 i.e. Q0 to Q13.Please help me in devising the approach so that I display the data in format below:

I want to display data like this:

County	Org	Metric	Q0	Q1	Q2
Ind	XYZ	Sale	10	12	33
AUS	abc	Sale	100	120	50
Ind	XYZ	Inhand	15	20	70
AUS	abc	Inhand	30	45	65
Ind	XYZ	Inventory	1000	800	1200
AUS	abc	Inventory	500	700	600

[chrismarlow]

I don't think you can use crosstable, so I think you have to do something like the attached.

[Anonymous]

Hi Fayez,

You can do it like this:

Directory;

AAAA:

CrossTable(Metric, Data, 2)

LOAD Country,

     Org,

     Sale0,

     sale1,

     Sale2,

     inhand0,

     inhand1,

     Inhand2,

     Inventory0,

     Inventory1,

     Inventory11

FROM

[Copia de Duplicate.xls]

(biff, embedded labels, table is Sheet1$);

Final:

LOAD

    Country,

    Org,

    Capitalize(PurgeChar(Metric,'1234567890')) as Metric,

    Data,

    'Q1'                                       as Quarter

RESIDENT AAAA

WHERE Metric = 'Sale0' or Metric = 'inhand0' OR Metric = 'Inventory0';

LOAD

    Country,

    Org,

    Capitalize(PurgeChar(Metric,'1234567890')) as Metric,

    Data,

    'Q2'                                       as Quarter

RESIDENT AAAA

WHERE Metric = 'sale1' or Metric = 'inhand1' OR Metric = 'Inventory1';

LOAD

    Country,

    Org,

    Capitalize(PurgeChar(Metric,'1234567890')) as Metric,

    Data,

    'Q3'                                       as Quarter

RESIDENT AAAA

WHERE Metric = 'Sale2' or Metric = 'Inhand2' OR Metric = 'Inventory11';

DROP TABLE AAAA;

But, If your table names are the same, only changing numbers, you can do it more dynamic:

Directory;

AAAA_Aux:

CrossTable(Metric, Data, 2)

LOAD Country,

     Org,

     Sale0,

     Sale1,

     Sale2,

     Inhand0,

     Inhand1,

     Inhand2,

     Inventory0,

     Inventory1,

     Inventory2

FROM

[Copia de Duplicate.xls]

(biff, embedded labels, table is Sheet1$);

AAAA:

NoConcatenate

LOAD * RESIDENT AAAA_Aux ORDER BY Metric;

DROP TABLE AAAA_Aux;

Aux1:

Load Distinct Purgechar(Metric,'1234567890') as NewMetric RESIDENT AAAA;

Let vNumRows = NoOfRows('Aux1');

FOR x = 0 to $(vNumRows)-1

    LET vMetric = Peek('NewMetric',$(x),'Aux1');

    Aux:

    Load Count(DISTINCT Metric) as Count RESIDENT AAAA WHERE Left(Metric,Len('$(vMetric)')) = '$(vMetric)';   

    LET vNum = Peek('Count',0,'Aux')-1;

    DROP TABLE Aux;

    FOR i = 0 TO $(vNum)

        Final:

        LOAD

            Country,

            Org,

            'Q'&($(i)+1)    as Quarter,

            Data,

            '$(vMetric)'    as Metric

        RESIDENT AAAA

        WHERE Metric = '$(vMetric)'&$(i);

    NEXT i;

NEXT x;

DROP TABLE AAAA;

Regards!!!

[pho3nix90]

Here you go,