Average calculation at the script level

[nevilledhamsiri]

Dear All!

Appreciate, if I'm helped with, to calculate average at the script level so that I need to be able to write a nested if statement to prepare a class  like (>30000, 20000-30000,10000-20000 ) etc ,based on the average computed at the script level. Since the value given is not the average the class function to be based on the average. Also, the average here should be the half of the value of each employee.

Thanks in advance

Neville

[Miguel_Angel_Baeyens]

Load the data normally in the script, then, using the key field or fields, LEFT JOIN another table with the aggregation for the average, something like

LEFT JOIN (Table) LOAD
EmployeeID,
Avg(Value) AS AvgValue, // if this value needs to be divided by two, do it here
Class(Avg(Value), 'Class1', 'Class2', Class3') AS ClassValue
RESIDENT Table;

[nevilledhamsiri]

Dear Migual,

Could you please see any synthetic error in my if statement at the script? I do not get it correctly at the back end.

Thanks

[Kushal_Chawda · Accepted solution]

change like below

MAIN:
LOAD BR_CODE, DATE, ME_NAME, VALUE

FROM [D:\ME_COMM.xlsx] (ooxml, embedded labels, table is Sheet1);

LEFT JOIN (MAIN)
LOAD
ME_NAME,BR_CODE,

avg(VALUE) AS AVGVALUE,
IF(avg(VALUE)>25000,'>25000',
IF(avg(VALUE)>20000,'20000-25000',
IF(avg(VALUE)>15000,'15000-20000',
IF(avg(VALUE)>10000,'10000-15000','<10000'))))AS CLASS_FLAG

RESIDENT MAIN
Group by ME_NAME,BR_CODE;

[nevilledhamsiri]

Hi Kush,

It worked. I need to be more thorough in script writing familiarity. Suppose I have two files where one file carry commissions details of employees & other carry their date joined. I need to calculate the average commissions based on the employee's age. If the age is >= 12 Months as at today, the amount to be the average of twelve Months if not the amount to be divided by number of Months which may be less than 12 Months. How the script to be structured like you did it for a situation only when a single table is loaded which is the base table.I did it to some far but getting a script error. Could you please help me on this?

Thanks

Neville 

[nevilledhamsiri]

Dear Miguel

Thanks for your reply which helped me to gain some idea to further my interest on this.

Neville

[Kushal_Chawda]

Do you need just for 12 months Avg or dynamic like if some employee completed 6 months so avg of 6 months?

[nevilledhamsiri]

Yes,

but it should be at the script itself

[nevilledhamsiri]

Should be varied depending on the age (Months) like you said to be dynamic

[Kushal_Chawda]

you can o something like below

MAIN:
LOAD BR_CODE, ME_CODE, NAME_1, POLI_NO, V_NO, RECEIPT_NO, NAME_2, AMOUNT, VOUCHER_NO
FROM [D:\COMM_DETAILS.xlsx] (ooxml, embedded labels, table is WORKING);

LEFT JOIN (MAIN)
LOAD [Branch Code], BRANCH, [ME Code] AS ME_CODE,Sales_Person_Name, [Date Joined], Designation, Premium,
((year(today())*12)+month(today())) - (((year([Date Joined])*12)+month([Date Joined]))) as Age_in_month,
Ceded_Premium, Ceded_Comm, [Net Revenue], Commission, [Net Claims], [Gross Profit],
[Staff Cost], [Other Cost], [Net Contribution], [Debtors & Rtd Cheques over 90 Days], Salary,
EPF, ETF, Gratuity, Bonus, Medical, Insurance, [Special Allowance], Telephone, Fuel,
[Vehicle Maintenence], Insurance1, Depreciation, [Lease Cost], F32, [Resigned Date], F34, F35,
F36, F37, F38
FROM [D:\DATE_JOINED.xlsx] (ooxml, embedded labels, table is D_J);

LEFT JOIN (MAIN)

LOAD
BR_CODE,ME_CODE,

SUM(AMOUNT)/Age_in_month AS AVG_COMM

RESIDENT MAIN

GROUP BY ME_CODE,BR_CODE;