Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I would really appreciate some guidance with the issue below:
I need to convert an excel spreadsheet into a digestible dataset. Currently, each month has its own column, with its revenue associated with it. I would like to covert all this into a singe column. This needs to be done in script, as my goal is to extract a qvd from this data to be consumed in other dashboards.
Essentially from this (excel):
Client Jan Feb Mar
ABC 1000 500 400
Into:
PERIOD CLIENT REVENUE
201601 ABC 1000
201602 ABC 500
201603 ABC 400
Thank you very much in advance.
Gustavo
Try with CrossTable's third parameter:
Table:
CrossTable (MONTH, REVENUE, 4)
LOAD Client,
User,
Region,
Product,
Jan16,
Feb16
4 equals the number of dimension you don't want to be transposed.
Help section of QlikView for CrossTable: QlikView ‒ Crosstable
HTH
Best,
Sunny
Try this:
Table:
CrossTable (MONTH, REVENUE)
LOAD * Inline [
CLIENT, Jan, Feb, Mar
ABC, 1000, 500, 400
];
FinalTable:
LOAD CLIENT,
REVENUE,
Date(MakeDate(2016, Month(Date#(MONTH, 'MMM'))), 'YYYYMM') as PERIOD
Resident Table;
DROP Table Table;
Thank you so much for this! It's certainly in the right direction. It works for 1 'dimension', CLIENT, in this case. What would be necessary in order to accommodate for extra items? The data actually looks like:
Client User Region Product Jan16 Feb16...
ABC John D NAM Prime 500 1000
...
Thank you
Try with CrossTable's third parameter:
Table:
CrossTable (MONTH, REVENUE, 4)
LOAD Client,
User,
Region,
Product,
Jan16,
Feb16
4 equals the number of dimension you don't want to be transposed.
Help section of QlikView for CrossTable: QlikView ‒ Crosstable
HTH
Best,
Sunny
Thank you, Sunny. I truly appreciate this.