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

[#1]

Hello QlikView Community

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

Project	Phase 1 Cost	Phase 2 Cost	Phase 4 Cost
A	3	3	3
B	4	5	2
C	5	2	5
D	1	5	2

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:

Project	Phase 1 Cost	Phase 2 Cost	Phase 4 Cost
A	3	3	3
B	4	5	2
C	5	2	5
D	1	5	2
Average	3,25	3,75	3

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

Thank you very much for reading 

[#2]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[#4]

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?

[Gysbert_Wassenaar]

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;

[#6]

Thank you very much.

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