Peek function and Rangesum not working as expected

[tetew89]

Hi everyone, I have this script where I want to get the average of the employees in the same city but for some reason the final table shows the column AvgAge with the same values as the usual Age column. I also do not understand entirely what the function RangeAvg(peek(AvgAge), Age) does. In theory it should take the average of the ages in each city but why is there a peek as well and the a , Age? Could you help me out please?

here is the code:

EmployeeDates:

Load * Inline [

EmployeeCode|City|FirstName|Age

101|"Rome"|"David"|36

102|"Rome"|"Maria"|22

103|"Lisbon"|"Lukas"|45

104|"Vienna"|"Jasmine"|51

105|"Lisbon"|"Tom"|31

106|"Vienna"|"Kevin"|27

] (delimiter is '|');

FirstEmployee:

Load *, if(City=Peek(City), RangeAvg(peek(AvgAge), Age), Age) as AvgAge

Resident EmployeeDates
order by City;

drop table EmployeeDates;
exit script;

[pravinboniface · Accepted solution]

@tetew89 I feel the following would be a better approach to achieve the same result.

EmployeeDates:

Load * Inline [
EmployeeCode|City|FirstName|Age
101|"Rome"|"David"|36
102|"Rome"|"Maria"|22
103|"Lisbon"|"Lukas"|45
104|"Vienna"|"Jasmine"|51
105|"Lisbon"|"Tom"|31
106|"Vienna"|"Kevin"|27
] (delimiter is '|');

left join (EmployeeDates)
Load City, avg(Age) as AvgAge
Resident EmployeeDates
group by City;
exit Script;

[tetew89]

That works! Thank you so much! I also have a similar script where I am doing a rangesum of the salaries within the same city. It is working fine but I dont understand what the  RangeSum(peek(AvgAnnualSales), AnnualSales) part does exactly. Would you be so kind to explain it to me?

EmployeeData:

Load * Inline [

EmployeeCode|City|FirstName|AnnualSales

101|"Rome"|"David"|3600

102|"Rome"|"Maria"|2200

103|"Lisbon"|"Lukas"|4500

104|"Vienna"|"Jasmine"|5100

105|"Lisbon"|"Tom"|3100

106|"Vienna"|"Kevin"|2700

] (delimiter is '|');

Employees:

Load City, if(City=Peek(City), RangeSum(peek(AvgAnnualSales), AnnualSales), AnnualSales) as AvgAnnualSales

Resident EmployeeData
order by City;

drop table EmployeeData;
exit script;

[pravinboniface]

@tetew89 This line in your second example is just maintaining a running total of the annual sales.  It's not really calculating and average (even though it's named AvgAnnual Sales.  If the City name is appearing for the first time, it takes the sales from that row.  If the next row is the same city, then it creates a running total by adding to the previous total.

 if(City=Peek(City), RangeSum(peek(AvgAnnualSales), AnnualSales), AnnualSales) as AvgAnnualSales

The first example is similar but instead of a running total, it tries to calculate a running average using RangeAvg instead of RangeSum.

Hope that helps!