Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Calculate average, and add it as a row in load script

Hello QlikView Community

I have data as follows, residing in an Excel spreadsheet:

ProjectPhase 1 Cost Phase 2 CostPhase 4 Cost
A333

B

452
C525
D152

In the LOAD script, I want to calculate the average of each Phase Cost, and have it appended to the end of the loaded data. To clarify, after the data has been loaded, I want the table to appear as follows:

ProjectPhase 1 Cost Phase 2 CostPhase 4 Cost
A333

B

452
C525
D15

2

Average3,253,753

What should the script look like to calculate the average of each Phase?

Thank you very much for reading 

1 Solution

Accepted Solutions

Re: Calculate average, and add it as a row in load script

Like this:

Tabel1:

Load

Project, Phase 1 Cost, Phase 2 Cost, Phase 4 Cost

from ...mysource...;

Load 'Average' as Project,

avg([Phase 1 Cost]) as [Phase 1 Cost],

avg([Phase 2 Cost]) as [Phase 2 Cost],

avg([Phase 4 Cost]) as [Phase 4 Cost]

resident Tabel1;

If you need to sort the result, sort by load order on Project.


talk is cheap, supply exceeds demand
5 Replies
Not applicable

Re: Calculate average, and add it as a row in load script

Try below script:

concatenate (exceldata)

Load 'Average'  as Project,avg(Phase1 cost) as Phase1 cost,avg(Phase2 cost) as Phase2 cost,avg(Phse4 cost) as Phase4 cost

resident exceldata

Re: Calculate average, and add it as a row in load script

Like this:

Tabel1:

Load

Project, Phase 1 Cost, Phase 2 Cost, Phase 4 Cost

from ...mysource...;

Load 'Average' as Project,

avg([Phase 1 Cost]) as [Phase 1 Cost],

avg([Phase 2 Cost]) as [Phase 2 Cost],

avg([Phase 4 Cost]) as [Phase 4 Cost]

resident Tabel1;

If you need to sort the result, sort by load order on Project.


talk is cheap, supply exceeds demand
Not applicable

Re: Calculate average, and add it as a row in load script

Thank you very much, this works 100%

What happens next though, is that I want to create a crosstable with Tabel1. How do I specify that the crosstable should be created FROM Tabel1 after the averages has been appended?

Re: Calculate average, and add it as a row in load script

The averages are appended to Tabel1, so that's the table name to use in the resident load:

Tabel2:

crosstable(Phase, Cost,1)

load * resident Tabel1;

You might want to rename some fields first:

crosstable(Phase, Cost,1)

load

     Project,

     [Phase 1 Cost] as 1,

     [Phase 2 Cost] as 2,

     [Phase 4 Cost] as 4

resident Tabel1;


talk is cheap, supply exceeds demand
Not applicable

Re: Calculate average, and add it as a row in load script

Thank you very much.

I am very new to QlikView and these solutions would take such a long time to figure out by yourself.

Community Browser