Perform an average in script if

[Anonymous]

Hi,

I want a new field that returns the average (n) if 'Apple' appears in the field value, otherwise return 'n'. I just can't figure it out.

Many thanks

[Or]

Average of what? What is n? Your sample doesn't really explain what the incoming data and expected outcome are.

[NoahF · Accepted solution]

If I understand your desired output correctly, then you could calculate the average(n) first and store it in a variable. Then you can load your entire table and decide with the wildmatch() function, if the Field includes the word '*Apple*', and save either the value of n or the avg(n) from the variable.

Data:
LOAD * INLINE [
Field, n
Green Apple 1, 6
Red Apple 2, 8
Pear 3, 4
Orange 2, 7
Pear 1, 5
];

Temp:
LOAD AVG(n) AS AvgN
RESIDENT Data;

Let vAverage = Peek('AvgN');


Avg:
LOAD *,
IF(WildMatch(Field, '*Apple*'), $(vAverage) , n) AS New_Field
RESIDENT Data;

Drop Table Data, Temp; 

Output Tabel:

[Anonymous]

Thank you so much!