Averaging Values While Loading

[#1]

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!!!

[Anonymous · Accepted solution]

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.

[Anonymous]

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;

[sujeetsingh]

The average you want is by element name .

See the sample

[#5]

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