topic How to format dates from multiple columns to a single Period column in QlikView

How to format dates from multiple columns to a single Period column

Wed, 20 Jan 2016 19:51:11 GMT

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

Re: How to format dates from multiple columns to a single Period column

sunny_talwar — Wed, 20 Jan 2016 20:22:59 GMT

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;

Re: How to format dates from multiple columns to a single Period column

Mon, 25 Jan 2016 21:53:12 GMT

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

Re: How to format dates from multiple columns to a single Period column

sunny_talwar — Mon, 25 Jan 2016 22:32:21 GMT

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

Re: How to format dates from multiple columns to a single Period column

Tue, 26 Jan 2016 14:20:08 GMT

Thank you, Sunny. I truly appreciate this.