Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
I don't think you can use crosstable, so I think you have to do something like the attached.
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!!!
Here you go,