Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following table from an Excel:
PERIOD | HOTEL | ROOMS AVAILABLE BUDGET | ROOMS AVAILABLE ACTUAL | ROOMS OCUPPIED BUDGET | ROOMS OCCUPIED ACTUAL | SALES BUDGET | SALES ACTUAL |
201807 | Hotel1 | 10 | 10 | 10 | 10 | 10 | 10 |
201807 | Hotel2 | 20 | 20 | 20 | 20 | 20 | 20 |
201807 | Hotel3 | 30 | 30 | 30 | 30 | 30 | 30 |
201708 | Hotel1 | 40 | 40 | 40 | 40 | 40 | 40 |
201708 | Hotel2 | 50 | 50 | 50 | 50 | 50 | 50 |
201708 | Hotel3 | 60 | 60 | 60 | 60 | 60 | 60 |
201709 | Hotel1 | 70 | 70 | 70 | 70 | 70 | 70 |
201709 | Hotel2 | 80 | 80 | 80 | 80 | 80 | 80 |
201709 | Hotel3 | 90 | 90 | 90 | 90 | 90 | 90 |
The thing is that I have many columns (each one is repeated by Budget or Actual).
Is there any way to transpose the table in order to have something like this? (and would it be better to handle this way??)
PERIOD | HOTEL | METRIC | TYPE | VALUE |
201807 | Hotel1 | ROOMS_AVAILABLE | BUDGET | 10 |
201807 | Hotel1 | ROOMS_AVAILABLE | ACTUAL | 10 |
201807 | Hotel2 | ROOMS_AVAILABLE | BUDGET | 20 |
201807 | Hotel2 | ROOMS_AVAILABLE | ACTUAL | 20 |
201807 | Hotel3 | ROOMS_AVAILABLE | BUDGET | 30 |
201807 | Hotel3 | ROOMS_AVAILABLE | ACTUAL | 30 |
201807 | Hotel1 | ROOMS_OCCUPIED | BUDGET | 10 |
201807 | Hotel1 | ROOMS_OCCUPIED | ACTUAL | 10 |
201807 | Hotel2 | ROOMS_OCCUPIED | BUDGET | 20 |
201807 | Hotel2 | ROOMS_OCCUPIED | ACTUAL | 20 |
201807 | Hotel3 | ROOMS_OCCUPIED | BUDGET | 30 |
201807 | Hotel3 | ROOMS_OCCUPIED | ACTUAL | 30 |
201807 | Hotel1 | SALES | BUDGET | 10 |
201807 | Hotel1 | SALES | ACTUAL | 10 |
201807 | Hotel2 | SALES | BUDGET | 20 |
201807 | Hotel2 | SALES | ACTUAL | 20 |
201807 | Hotel3 | SALES | BUDGET | 30 |
201807 | Hotel3 | SALES | ACTUAL | 30 |
201808 | Hotel1 | ROOMS_AVAILABLE | BUDGET | 40 |
201808 | Hotel1 | ROOMS_AVAILABLE | ACTUAL | 40 |
201808 | Hotel2 | ROOMS_AVAILABLE | BUDGET | 50 |
201808 | Hotel2 | ROOMS_AVAILABLE | ACTUAL | 50 |
201808 | Hotel3 | ROOMS_AVAILABLE | BUDGET | 60 |
201808 | Hotel3 | ROOMS_AVAILABLE | ACTUAL | 60 |
201808 | Hotel1 | ROOMS_OCCUPIED | BUDGET | 40 |
201808 | Hotel1 | ROOMS_OCCUPIED | ACTUAL | 40 |
201808 | Hotel2 | ROOMS_OCCUPIED | BUDGET | 50 |
201808 | Hotel2 | ROOMS_OCCUPIED | ACTUAL | 50 |
201808 | Hotel3 | ROOMS_OCCUPIED | BUDGET | 60 |
201808 | Hotel3 | ROOMS_OCCUPIED | ACTUAL | 60 |
Or would it be better to use it as I get it? (I only put 3 metrics for this demo, but I actually have like 7 or 😎
Thanks in advance!!
You can do something like this
Table: CrossTable (METRIC, VALUE, 2) LOAD * INLINE [ PERIOD, HOTEL, ROOMS AVAILABLE BUDGET , ROOMS AVAILABLE ACTUAL , ROOMS OCUPPIED BUDGET , ROOMS OCCUPIED ACTUAL , SALES BUDGET , SALES ACTUAL 201807, Hotel1, 10, 10, 10, 10, 10, 10 201807, Hotel2, 20, 20, 20, 20, 20, 20 201807, Hotel3, 30, 30, 30, 30, 30, 30 201708, Hotel1, 40, 40, 40, 40, 40, 40 201708, Hotel2, 50, 50, 50, 50, 50, 50 201708, Hotel3, 60, 60, 60, 60, 60, 60 201709, Hotel1, 70, 70, 70, 70, 70, 70 201709, Hotel2, 80, 80, 80, 80, 80, 80 201709, Hotel3, 90, 90, 90, 90, 90, 90 ]; FinalTable: LOAD PERIOD, HOTEL, Left(METRIC, Index(METRIC, ' ', -1)-1) as METRIC, SubField(METRIC, ' ', -1) as TYPE, VALUE Resident Table; DROP Table Table;
You can do something like this
Table: CrossTable (METRIC, VALUE, 2) LOAD * INLINE [ PERIOD, HOTEL, ROOMS AVAILABLE BUDGET , ROOMS AVAILABLE ACTUAL , ROOMS OCUPPIED BUDGET , ROOMS OCCUPIED ACTUAL , SALES BUDGET , SALES ACTUAL 201807, Hotel1, 10, 10, 10, 10, 10, 10 201807, Hotel2, 20, 20, 20, 20, 20, 20 201807, Hotel3, 30, 30, 30, 30, 30, 30 201708, Hotel1, 40, 40, 40, 40, 40, 40 201708, Hotel2, 50, 50, 50, 50, 50, 50 201708, Hotel3, 60, 60, 60, 60, 60, 60 201709, Hotel1, 70, 70, 70, 70, 70, 70 201709, Hotel2, 80, 80, 80, 80, 80, 80 201709, Hotel3, 90, 90, 90, 90, 90, 90 ]; FinalTable: LOAD PERIOD, HOTEL, Left(METRIC, Index(METRIC, ' ', -1)-1) as METRIC, SubField(METRIC, ' ', -1) as TYPE, VALUE Resident Table; DROP Table Table;