Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

calculate Price Index in script

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

1 Solution

Accepted Solutions
MVP
MVP

Re: calculate Price Index in script

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 Replies
MVP
MVP

Re: calculate Price Index in script

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;

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

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

Not applicable

Re: calculate Price Index in script

PFA

Not applicable

Re: calculate Price Index in script

Great job, you helped me a lot. Thanks

Community Browser