Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..!!!
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
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;
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])
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
Thanks Max..