topic Re: Loading Average Values from table to Qlikview in QlikView

Loading Average Values from table to Qlikview

Thu, 11 Sep 2014 09:28:20 GMT

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..!!!

Re: Loading Average Values from table to Qlikview

MK_QSL — Thu, 11 Sep 2014 09:33:48 GMT

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;

Re: Loading Average Values from table to Qlikview

MK_QSL — Thu, 11 Sep 2014 09:35:42 GMT

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])

Re: Loading Average Values from table to Qlikview

PrashantSangle — Thu, 11 Sep 2014 09:38:26 GMT

Hi,

It is easily you can acheive.

In load script create Year Field

using Year()

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

Re: Loading Average Values from table to Qlikview

Thu, 11 Sep 2014 10:00:12 GMT

Thanks Max..