Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

Handle Excel with Multiple Columns

Hello,

I have the following table from an Excel:

PERIODHOTEL ROOMS_AVAILABLE_BUDGET  ROOMS_AVAILABLE_ACTUAL  ROOMS_OCUPPIED_BUDGET  ROOMS_OCCUPIED_ACTUAL  ROOM_SALES_BUDGET  ROOM_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
201807Hotel1ROOM_SALESBUDGET                                                      10
201807Hotel1ROOM_SALESACTUAL                                                      10
201807Hotel2ROOM_SALESBUDGET                                                      20
201807Hotel2ROOM_SALESACTUAL                                                      20
201807Hotel3ROOM_SALESBUDGET                                                      30
201807Hotel3ROOM_SALESACTUAL                                                      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 Reply
zzyjordan
Creator II
Creator II

Hi, 

Try this script to load your data

tmpData:
CrossTable(Metrics,Value,2)

LOAD
PERIOD,
HOTEL,
" ROOMS_AVAILABLE_BUDGET ",
" ROOMS_AVAILABLE_ACTUAL ",
" ROOMS_OCUPPIED_BUDGET ",
" ROOMS_OCCUPIED_ACTUAL ",
" ROOM_SALES_BUDGET ",
" ROOM_SALES_ACTUAL "
FROM [lib://AttachedFiles/0109.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

FinalData:
Load
PERIOD,
HOTEL,
Mid(Metrics,1,Index(Metrics,'_',2)-1) as "Metrics",
Mid(Metrics,Index(Metrics,'_',2)+1) as "Type",
Value
Resident tmpData;

drop Table tmpData;

 

Untitled.jpg

 

Hope this helps

ZZ