Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

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

Load

country,

Population / Sum( Population )  as [Percentage]

resident Populations

Group by country;

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

But Get Erros :Invalid expression

1 Solution

Accepted Solutions

Re: Calculate Percentage in load script(Resident Load)

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.

4 Replies
MVP
MVP

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)

LOAD country,

          Sum(Population) as TotalPopulation

Resident Populations

Group By country;

[Calculate Percentage]:

Load *,

        Population/TotalPopulation  as [Percentage]

Resident Populations;

DROP Table Populations

ramoncova06
Valued Contributor III

Re: Calculate Percentage in load script(Resident Load)

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;

Re: Calculate Percentage in load script(Resident Load)

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.

MVP
MVP

Re: Calculate Percentage in load script(Resident Load)

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;

1.png

Community Browser