Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Dears
I want Calculate percent the following file Calculate Percentage in load script(Resident Load)
---------------------------------------------
Populations:
Load *, Num( Population) as pop;
LOAD [% of world population],
country,
Date,
Population,
Rank,
Source
FROM
TB01_20150721_222616.xls
(biff, embedded labels, table is [Sheet1$]);
[Calculate Percentage]:
Load
country,
Population / Sum( Population ) as [Percentage]
resident Populations
Group by country;
---------------------------------------------
But Get Erros :Invalid expression
Here's another way:
Populations:
Load *, Num( Population) as pop;
LOAD Rank,
[Country (or dependent territory)] as country,
Date,
Population,
[% of world population],
Source
FROM
[https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population]
(html, codepage is 1256, embedded labels, table is @1);
Population_Sum:
LOAD sum(Population) as Sum_Population
RESIDENT Populations;
LET vPopulationSum = PEEK('Sum_Population',0,'Population_Sum');
DROP TABLE Population_Sum;
CalculatePercentage:
Load
country,
Population / $(vPopulationSum) as [Percentage]
resident Populations;
Example file attached.
Try this:
Populations:
LOAD [% of world population],
country,
Date,
Population,
Rank,
Source
FROM
TB01_20150721_222616.xls
(biff, embedded labels, table is [Sheet1$]);
Join(Populations)
LOAD country,
Sum(Population) as TotalPopulation
Resident Populations
Group By country;
[Calculate Percentage]:
Population/TotalPopulation as [Percentage]
Resident Populations;
DROP Table Populations
try with a map
MAPPING
MappingPopulations:
LOAD country,
SUM(Population) AS TotalPopulation
FROM
TB01_20150721_222616.xls
(biff, embedded labels, table is [Sheet1$])
GROUP BY country;
Populations:
Load *, Num( Population) as pop;
LOAD [% of world population],
country,
Date,
Population,
Rank,
Source
FROM
TB01_20150721_222616.xls
(biff, embedded labels, table is [Sheet1$]);
[Calculate Percentage]:
Load
country,
Population / APPLYMAP('MappingPopulations',country) as [Percentage]
resident Populations;
Here's another way:
Populations:
Load *, Num( Population) as pop;
LOAD Rank,
[Country (or dependent territory)] as country,
Date,
Population,
[% of world population],
Source
FROM
[https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population]
(html, codepage is 1256, embedded labels, table is @1);
Population_Sum:
LOAD sum(Population) as Sum_Population
RESIDENT Populations;
LET vPopulationSum = PEEK('Sum_Population',0,'Population_Sum');
DROP TABLE Population_Sum;
CalculatePercentage:
Load
country,
Population / $(vPopulationSum) as [Percentage]
resident Populations;
Example file attached.
Populations: LOAD
[Country (or dependent territory)] as country,
Population as pop,
alt(Peek(cumpop),0)+Population as cumpop
FROM
[https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population]
(html, codepage is 1256, embedded labels, table is @1);
Final: LOAD
country,
pop,
alt(peek(totpop), cumpop) as totpop,
pop / alt(peek(totpop), cumpop) as %pop
Resident Populations
order by cumpop desc;
DROP Table Populations;