calculate Price Index in script

[#1]

Hi everybody,

In the attached QVW I have 3 variables: Country, Year and #Price.

For each Country I want to calculate in the LOAD Script the Price index based on the first year of data. Each country has a different start year.

The calculation must be something like this, per country: ('#Price concurrent year' / '#Price first year') * 100

Can somebody help me to integrate this in the LOAD Script. I also attached the data source in excel.

Your help is appreciated! Arthur

[maxgro · Accepted solution]

some little changes (bold) to your script to add the column #PriceFirstYear in every rows

Price_tmp:

CrossTable(Year, #data)

LOAD *

FROM

[tsiir020 frozen as of 21 Dec 2011.xls]

(biff, embedded labels, table is Sheet0$);

Price2_tmp:

LOAD

Country,

num#(Year) as Year,

#data as #Price

Resident Price_tmp Where [#data]<> ':' ;

Left Join (Price2_tmp)

LOAD

  Country,

  FirstSortedValue(#Price, Year) as #PriceFirstYear,

  FirstSortedValue(Year, Year) as #FirstYear

Resident

  Price2_tmp

Group By

  Country;

DROP Table Price_tmp; // Price2_tmp;

.......................

.......................

[#3]

PFA

[#4]

Great job, you helped me a lot. Thanks