topic Re: calculate Price Index in script in QlikView

calculate Price Index in script

Sat, 22 Feb 2014 09:18:01 GMT

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 — Sat, 22 Feb 2014 10:05:47 GMT

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;

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

Sat, 22 Feb 2014 10:46:07 GMT

PFA

Sat, 22 Feb 2014 13:52:28 GMT

Great job, you helped me a lot. Thanks