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

[#1]

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

[sunny_talwar]

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;

[#3]

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

[sunny_talwar · Accepted solution]

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

[#5]

Thank you, Sunny. I truly appreciate this.