Loading Average Values from table to Qlikview

[#1]

I have a kind of following table in excel sheet.

Country	Month.Year	Population	Life Expectancy
A	06.2011	1000	70
A	12.2011	1400	72
A	06.2012	2000	74
A	12.2012	2200	76
B	06.2011	1200	68
B	12.2011	1400	70
B	06.2012	1600	72
B	12.2012	1800	74

While loading the information from this table, I want the following kind to tebale format loaded to the QlikView. The values of population and life expectancy should be averaged for each city for each year.

Country	Year	Avg Population	Avg Life Expectancy
A	2011	1200	71
A	2012	2100	75
B	2011	1300	69
B	2012	1700	73

How should the load statement look like..?? Please help..

Thanks in advance..!!!

[MK_QSL]

Temp:

Load *, Right(Month.Year,4) as Year Inline

[

  Country, Month.Year, Population, Life Expectancy

  A, 06.2011, 1000, 70

  A, 12.2011, 1400, 72

  A, 06.2012, 2000, 74

  A, 12.2012, 2200, 76

  B, 06.2011, 1200, 68

  B, 12.2011, 1400, 70

  B, 06.2012, 1600, 72

  B, 12.2012, 1800, 74

];

Final:

Load

  Country,

  Year,

  Avg(Population) as [Avg Population],

  Avg([Life Expectancy]) as [Avg Life Expectancy]

Resident Temp

Group By Country, Year;

Drop Table Temp;

[MK_QSL]

On the Front End ...

Use below Script...

Temp:

Load *, Right(Month.Year,4) as Year Inline

[

  Country, Month.Year, Population, Life Expectancy

  A, 06.2011, 1000, 70

  A, 12.2011, 1400, 72

  A, 06.2012, 2000, 74

  A, 12.2012, 2200, 76

  B, 06.2011, 1200, 68

  B, 12.2011, 1400, 70

  B, 06.2012, 1600, 72

  B, 12.2012, 1800, 74

];

Create a Straight Table

Dimension

Country

Year

Expressions

1) Avg(Population)

2) Avg([Life Expectancy])

[PrashantSangle · Accepted solution]

Hi,

It is easily you can acheive.

In load script create Year Field

using Year()

Like

Load *,

Year(Month.Year) as newYear     //If your MOnth.year field is Date format if not then

Year(Date#(Month.Year,'MM.YYYY')) as NewYear1 //or you can

subfield(Month.Year,'.',1) as NewYear2

From tablename

Then

In GUI

use Straight Table chart

USe Country and one of the NewYear Field as your Dimension

and

In Expression Write

1) Avg(Population)

2)Avg([Life Expentancy])

It will give you desired result.

Regards

[#5]

Thanks Max..