crosstable / LOAD question

[#1]

Hi all,

i am loading data from an Excel file the following table structure:

Player	2011 Result	2012 Aim	2012 Result	2013 Aim	2013 Result	2012 Q1 Aim	2012 Q1 Result	2012 Q2 Aim	2012 Q2 Result	2012 Q3 Aim	2012 Q3 Result	2012 Q4 Aim	2012 Q4 Result
John Smith	175.000	200.000		220.000		33.000	39.000	42.000	37.000	40.000		44.000
Steve Miller	125.000	160.000		188.000		23.000	25.000	26.000	22.000	30.000		31.000

I want to convert the table structure above into the following table structure:

Player	Interval Type	Year	Quarter	Aim	Result
John Smith	Quarterly	2012	Q1	33.000	39.000
John Smith	Quarterly	2012	Q2	42.000	37.000
John Smith	Quarterly	2012	Q3	40.000
John Smith	Quarterly	2012	Q4	44.000
John Smith	Yearly	2012		200.000
John Smith	Yearly	2013		220.000

etc...

How can this be done ? I have played around with Crosstable LOADS but nevver really could aschive the desired table structure.

Thanks a lot for help!

K

[Anonymous]

You might want to try loading it in separate cross tables and joining the resulting tables up to create the desired structure

[#3]

1. is it necessary to take two column : Aim and Result ?

     can't it by one column Type with values {Aim, Result}

if yes try to look at qvw file i've posted - all you have to do is get the synthetic key out;) - if you woun't be able to do it yourself, sen me a message - i will try to help you:)