Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following table
Year | Quarter | Month | F1 | F2 | F3 | F4 | F5 |
FY-2017/18 | Q1 | April | 0 | 1 | 0 | ||
FY-2017/18 | Q1 | May | 0 | 0 | 0 | ||
FY-2017/18 | Q2 | June | 0 | 0 | 0 |
I want to transpose it like following Sample data is attached). can anybody help please? thanks.
Required Table | |||
Year | Quarter | Month | Category |
FY-2017/18 | Q1 | April | F1 |
FY-2017/18 | Q1 | April | F2 |
FY-2017/18 | Q1 | April | F3 |
FY-2017/18 | Q1 | April | F4 |
FY-2017/18 | Q1 | April | F5 |
FY-2017/18 | Q1 | May | F1 |
FY-2017/18 | Q1 | May | F2 |
FY-2017/18 | Q1 | May | F3 |
FY-2017/18 | Q1 | May | F4 |
FY-2017/18 | Q1 | May | F5 |
FY-2017/18 | Q2 | June | F1 |
FY-2017/18 | Q2 | June | F2 |
FY-2017/18 | Q2 | June | F3 |
FY-2017/18 | Q2 | June | F4 |
FY-2017/18 | Q2 | June | F5 |
This load script:
Data:
CrossTable (Category, Flag, 3)
LOAD Year,
Quarter,
Month,
F1,
F2,
F3,
F4,
F5
FROM sample.xlsx (ooxml, embedded labels, header is 1 lines, table is Sheet1);
Gives this output:
Year | Quarter | Month | Category | Flag |
---|---|---|---|---|
FY-2017/18 | Q1 | April | F1 | 0 |
FY-2017/18 | Q1 | April | F2 | 1 |
FY-2017/18 | Q1 | April | F3 | 0 |
FY-2017/18 | Q1 | April | F4 | |
FY-2017/18 | Q1 | April | F5 | |
FY-2017/18 | Q1 | May | F1 | 0 |
FY-2017/18 | Q1 | May | F2 | 0 |
FY-2017/18 | Q1 | May | F3 | 0 |
FY-2017/18 | Q1 | May | F4 | |
FY-2017/18 | Q2 | June | F1 | 0 |
FY-2017/18 | Q2 | June | F2 | 0 |
FY-2017/18 | Q2 | June | F3 | 0 |
FY-2017/18 | Q2 | June | F4 | |
FY-2017/18 | Q2 | June | F5 |
This load script:
Data:
CrossTable (Category, Flag, 3)
LOAD Year,
Quarter,
Month,
F1,
F2,
F3,
F4,
F5
FROM sample.xlsx (ooxml, embedded labels, header is 1 lines, table is Sheet1);
Gives this output:
Year | Quarter | Month | Category | Flag |
---|---|---|---|---|
FY-2017/18 | Q1 | April | F1 | 0 |
FY-2017/18 | Q1 | April | F2 | 1 |
FY-2017/18 | Q1 | April | F3 | 0 |
FY-2017/18 | Q1 | April | F4 | |
FY-2017/18 | Q1 | April | F5 | |
FY-2017/18 | Q1 | May | F1 | 0 |
FY-2017/18 | Q1 | May | F2 | 0 |
FY-2017/18 | Q1 | May | F3 | 0 |
FY-2017/18 | Q1 | May | F4 | |
FY-2017/18 | Q2 | June | F1 | 0 |
FY-2017/18 | Q2 | June | F2 | 0 |
FY-2017/18 | Q2 | June | F3 | 0 |
FY-2017/18 | Q2 | June | F4 | |
FY-2017/18 | Q2 | June | F5 |