Crosstable multiple months as columns

[ymcmb]

I have a dataset that I am trying to pivot in order to use for analysis. My data set contains revenue for a salesman during a 3 year duration. No year is provided, except months 0-35. 

The columns are: M0, M1, M2, M3 -----> M35. Revenue is provided for each month for each salesman.

What is the best way to pivot this dataset so that I can use both Month and Year? I can use an arbitrary start date that will encapsulate the 3 years.

Data example:

[temp_table]:

Load inline [

Salesman, M0, M1, M2, ………

Josh, 300, 200, 100,

Amy, 600, 200, 100

]

[ymcmb]

Alot of manual changes were made. Since I had 4 separate fact sheets, I went ahead and created a master table that housed my dimensions, and went ahead concatenated 4 separate fact tables using the crossTable function which ultimately creates my star schema model.

[sidhiq91]

@ymcmb  Not sure if you have already figured out the solution by yourself. If not please see the code below:

NoConcatenate
[temp_table]:
Load * inline [
Salesman, M0, M1, M2,

Josh, 300, 200, 100,

Amy, 600, 200, 100
];

Temp:
CrossTable(Month,Sales)
Load *
Resident [temp_table];
Drop table [temp_table];

Exit Script;

Let me know if you need any more help on the same.

[ymcmb]

Hey, this is pretty much what I had done with my 4 fact columns. I crosstable them, perform a resident load, and create a unique key within each of my 4 fact tables to relate to my master dimension table that houses my Months and Years. All is working as expected.