Crosstable

[#1]

Hi,

I'm trying to create a table in the format :

Year, Item, Value/Qty and Month

At the moment it has a value for each month going horizontally instead a long skinny format.

It is currently in the format:

Year	Item	SMQ01	SMQ02	SMQ03	SMQ04	SMQ05	SMQ06	SMQ07	SMQ08	SMQ09	SMQ10	SMQ11	SMQ12
2010	a	0	0	0	0	0	1900	780	0	0	0	0	0
2011	a	0	0	0	1830	2045	0	0	1951	2285	1325	1691	1700
2012	a	832	1039	1039	0	0	0	0	0	0	0	0	0

Required format :

Year	Item	Month	Value
2012	a	1	832
2012	a	2	1039
2012	a	3	1039

Any ideas?

Many thanks

[renjithpl]

Try

Root:
CrossTable(Month, Value, 2)
LOAD Year,
     Item,
     SMQ01,
     SMQ02,
     SMQ03,
     SMQ04,
     SMQ05,
     SMQ06,
     SMQ07,
     SMQ08,
     SMQ09,
     SMQ10,
     SMQ11,
     SMQ12
FROM
crosstable.xlsx
(ooxml, embedded labels, table is Sheet1);

New:
load
*,
Right(Month, 2) as MonthNew
Resident Root;
drop Table Root;

[#3]

hi

try this,

a:

CrossTable(month, value, 2)

LOAD year,

     item,

     SMQ01,

     SMQ02,

     SMQ03,

     SMQ04,

     SMQ05,

     SMQ06,

     SMQ07

FROM

C:\crosstable.xlsx

(ooxml, embedded labels, table is Sheet1);

b:

load

value,

Right(month, 2) as month2

Resident a;

drop Table a;

hope this helps you.

[#4]

hi

please see the attached file.this file show the output like this

Required format :

Year	Item	Month	Value
2012	a	1	832
2012	a	2	1039
2012	a	3	1039