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

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

Sun, 17 Feb 2013 13:04:19 GMT

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

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

Sun, 17 Feb 2013 13:22:15 GMT

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

Gysbert_Wassenaar — Sun, 17 Feb 2013 13:23:55 GMT

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.

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

Sun, 17 Feb 2013 14:49:42 GMT

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

Gysbert_Wassenaar — Sun, 17 Feb 2013 15:20:10 GMT

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;

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

Sun, 17 Feb 2013 18:13:02 GMT

Thank you very much.

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