Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
microwin88x
Contributor III

Cross Table - Excel with Many Columns

Hello,

I have the following table from an Excel:

PERIODHOTEL ROOMS AVAILABLE BUDGET  ROOMS AVAILABLE ACTUAL  ROOMS OCUPPIED BUDGET  ROOMS OCCUPIED ACTUAL SALES BUDGET SALES ACTUAL 
201807Hotel1                                                      10                                                            10                   10                                                       10                   10                   10
201807Hotel2                                                      20                                                            20                   20                                                       20                   20                   20
201807Hotel3                                                      30                                                            30                   30                                                       30                   30                   30
201708Hotel1                                                      40                                                            40                   40                                                       40                   40                   40
201708Hotel2                                                      50                                                            50                   50                                                       50                   50                   50
201708Hotel3                                                      60                                                            60                   60                                                       60                   60                   60
201709Hotel1                                                      70                                                            70                   70                                                       70                   70                   70
201709Hotel2                                                      80                                                            80                   80                                                       80                   80                   80
201709Hotel3                                                      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??)

PERIODHOTELMETRICTYPE VALUE 
201807Hotel1ROOMS_AVAILABLEBUDGET                                                      10
201807Hotel1ROOMS_AVAILABLEACTUAL                                                      10
201807Hotel2ROOMS_AVAILABLEBUDGET                                                      20
201807Hotel2ROOMS_AVAILABLEACTUAL                                                      20
201807Hotel3ROOMS_AVAILABLEBUDGET                                                      30
201807Hotel3ROOMS_AVAILABLEACTUAL                                                      30
201807Hotel1ROOMS_OCCUPIEDBUDGET                                                      10
201807Hotel1ROOMS_OCCUPIEDACTUAL                                                      10
201807Hotel2ROOMS_OCCUPIEDBUDGET                                                      20
201807Hotel2ROOMS_OCCUPIEDACTUAL                                                      20
201807Hotel3ROOMS_OCCUPIEDBUDGET                                                      30
201807Hotel3ROOMS_OCCUPIEDACTUAL                                                      30
201807Hotel1SALESBUDGET                                                      10
201807Hotel1SALESACTUAL                                                      10
201807Hotel2SALESBUDGET                                                      20
201807Hotel2SALESACTUAL                                                      20
201807Hotel3SALESBUDGET                                                      30
201807Hotel3SALESACTUAL                                                      30
201808Hotel1ROOMS_AVAILABLEBUDGET                                                      40
201808Hotel1ROOMS_AVAILABLEACTUAL                                                      40
201808Hotel2ROOMS_AVAILABLEBUDGET                                                      50
201808Hotel2ROOMS_AVAILABLEACTUAL                                                      50
201808Hotel3ROOMS_AVAILABLEBUDGET                                                      60
201808Hotel3ROOMS_AVAILABLEACTUAL                                                      60
201808Hotel1ROOMS_OCCUPIEDBUDGET                                                      40
201808Hotel1ROOMS_OCCUPIEDACTUAL                                                      40
201808Hotel2ROOMS_OCCUPIEDBUDGET                                                      50
201808Hotel2ROOMS_OCCUPIEDACTUAL                                                      50
201808Hotel3ROOMS_OCCUPIEDBUDGET                                                      60
201808Hotel3ROOMS_OCCUPIEDACTUAL                                                      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!!

1 Solution

Accepted Solutions

Re: Cross Table - Excel with Many Columns

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;

View solution in original post

1 Reply

Re: Cross Table - Excel with Many Columns

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;

View solution in original post