CorssTable Problem

[simonagheo]

Hello,

I have an excel file structured like this:

Key	…	Month-1	Month-2	Month-3	…
A	…	100	100	300	…
B	…	200	200	200	…

and I want to load it using crosstable. I made the attached script for this.

tmp table is loaded ok, the problem is that in tmp2 table the Amount column is equal with period column:

Key	Period	Amount	Amount should be
A	1	1	100
B	1	1	200
A	2	2	100
B	2	2	200
A	3	3	300
B	3	3	200

What is wrong with my crosstable?

Thank you!

[#2 · Accepted solution]

Temp:

Load * Inline [

Key, Month-1, Month-2, Month-3

A, 100, 100, 300

B, 200, 200, 200

];

Temp2:

CrossTable(Period, Amount, 1)

Load *

Resident Temp;

Drop table Temp;

is this something you are looking for?

[simonagheo]

In the period column I want to have other values like 1,2,3,...,12

[simonagheo]

I changed tmp2 script like you said, I put * instead of all field names (it's not the same thing?) and I added the third parameter at crosstable function and it's working now. Thank you!