Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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 |