4 Replies Latest reply: Jul 22, 2015 2:04 PM by Massimo Grossi

# Calculate Percentage in load script(Resident Load)

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

country,

Population / Sum( Population )  as [Percentage]

resident Populations

Group by country;

---------------------------------------------

But Get Erros :Invalid expression

• ###### Re: Calculate Percentage in load script(Resident Load)

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)

Sum(Population) as TotalPopulation

Resident Populations

Group By country;

[Calculate Percentage]:

Population/TotalPopulation  as [Percentage]

Resident Populations;

DROP Table Populations

• ###### Re: Calculate Percentage in load script(Resident Load)

try with a map

MAPPING

MappingPopulations:

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

country,

Population / APPLYMAP('MappingPopulations',country) as [Percentage]

resident Populations;

• ###### Re: Calculate Percentage in load script(Resident Load)

Here's another way:

Populations:

Load *, Num(  Population) as pop;

[Country (or dependent territory)] as country,

Date,

Population,

[% of world population],

Source

FROM

(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:

country,

Population / \$(vPopulationSum)  as [Percentage]

resident Populations;

Example file attached.

• ###### Re: Calculate Percentage in load script(Resident 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);

country,

pop,

alt(peek(totpop), cumpop) as totpop,

pop / alt(peek(totpop), cumpop) as %pop

Resident Populations

order by cumpop desc;

DROP Table Populations;