Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
Average of what? What is n? Your sample doesn't really explain what the incoming data and expected outcome are.
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:
Thank you so much!