Bar Chart dimensions using multiple rows of dat

[Sam_Thomas]

Hi

I have a problem. I need to create a simple bar chart using the data below - but the dimensions I want are 5 different rows of data. I want to know if and how it's possible to do. 

FirstYearCostProfile	SecondYearCostProfile	ThirdYearCostProfile	FourthYearCostProfile	FifthYearCostProfile
0.2	0.7	0.1	0	0
0.1	0.7	0.2	0	0
1	0	0	0	0
0.1	0.3	0.4	0.2	0
0.05	0.1	0.6	0.2	0.05
1	0	0	0	0
0.05	0.75	0.2	0	0
0.3	0.7	0	0	0
0.3	0.7	0	0	0
0.3	0.7	0	0	0
0.3	0.7	0	0	0
0.25	0.75	0	0	0
0.2	0.7	0.1	0	0

The numbers represent the percentage of the budget spent in that particular year. So for example, the first line - 20% of the cost is spent in year one, 70% in year two etc. 

I have the actual financial data in the same table. I just want to see if I can create a bar chart with each year (1-5) being a dimension and the length of the bar chart is the sum of the money used in years 1, 2, 3, etc. As I said, I do have the actual figures. I have a COST column with the overal amount. So I can  multiply by the values in the fields above. 

Anyone have any ideas? Many thanks in advance. 

[Lisa_P · Accepted solution]

You can use the crosstable prefix in the load statement to unpivot the columns you want 

[Sam_Thomas]

Thanks for your reply. Your solution has helped, however, I can't quite get it perfect. If I add the crosstable function in the loadscript, it performs the action on the whole table. The only way I have got it to work is by deleting the other fields and only having the qualifier field and the fields I want to transpose. Do you know how I would get around this. Have copied the list of fields below. 

[SchemeMaster]:

CROSSTABLE(CostYear, Spend, 1)
LOAD
SchemeID,
TotCapexWithAdj * FirstYearCostProfile as [Year 1],
TotCapexWithAdj * SecondYearCostProfile as [Year 2],
TotCapexWithAdj * ThirtYearCostProfile as [Year 3],
TotCapexWithAdj * FourthYearCostProfile as [Year 4],
TotCapexWithAdj * FifthYearCostProfile as [Year 5]

But I also need these 20+ other fields from the same above table. I'm not sure how to structure the syntax/scripting. 

Thanks