Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Perform an average in script if

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.

AvgInScript.PNG

Many thanks

Labels (2)
1 Solution

Accepted Solutions
NoahF
Contributor III
Contributor III

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:
NoahF_0-1733859510060.png

 

View solution in original post

3 Replies
Or
MVP
MVP

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

 

NoahF
Contributor III
Contributor III

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:
NoahF_0-1733859510060.png

 

Anonymous
Not applicable
Author

Thank you so much!