topic Re: Averaging Values While Loading in QlikView

Averaging Values While Loading

Wed, 23 Jul 2014 10:56:32 GMT

Hello All,

I have the given dataset:

Element Name Element_Metric

AAA 98

AAA 95

AAA 96

AAA -

BBB 96

BBB 92

While loading, I want to create a calculated field (Avg_Metric) where I average Element_Metric for each non-missing 'Element Name'. So the desired result is:

Element Name Element_Metric Avg_Metric

AAA 98 96.3

AAA 95 96.3

AAA 96 96.3

AAA - -

BBB 96 94

BBB 92 94

I have tried the following:

Final:

Load [Element Name],

Element_Metric,

avg(Element_Metric) as Avg_Metric

Resident Temp

Group by [Element Name], Element_Metric;

This code is giving me the same values as 'Element_Metric'. I have tried other permutations and combinations as well, but nothing seems to be working.

What am I doing wrong? Please help!!!

Re: Averaging Values While Loading

Anonymous — Wed, 23 Jul 2014 11:00:18 GMT

This should give you a table of Avg_Metric per [Element Name]

Final:

Load [Element Name],

avg(Element_Metric) as Avg_Metric

Resident Temp

Group by [Element Name]

Then you need to join it to your original table, either with a join or ApplyMap.

Re: Averaging Values While Loading

Anonymous — Wed, 23 Jul 2014 11:14:24 GMT

Hi Nipika,

Try This Code

Tab126743:

LOAD * Inline [
Element Name,Element_Metric
AAA,98
AAA,95
AAA,96
AAA,-
BBB,96
BBB,92
];

Load
Element_Metric,
Avg(Element_Metric) as Avg_Met
Resident Tab126743 Group by Element_Metric;

Re: Averaging Values While Loading

sujeetsingh — Wed, 23 Jul 2014 11:16:13 GMT

The average you want is by element name .

See the sample

Re: Averaging Values While Loading

Wed, 23 Jul 2014 12:04:46 GMT

Thank you so much guys. That worked! Can't believe I spent hours on this!!