Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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?
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;
Thank you very much.
I am very new to QlikView and these solutions would take such a long time to figure out by yourself.