Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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.
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.
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;
The average you want is by element name .
See the sample
Thank you so much guys. That worked! Can't believe I spent hours on this!!