need logic help to calculate upper 10 rows average with a condition.

[lalitkgehlot89]

Hi,

In sample data i have transit loss. in some rows transit loss value is blank. I want average transit loss of previous ten rows  in blank cell of transit loss field, with condition only previous 10 row with data will be consider .

Please help me out to write the logic.

thanks

Lalit Kumar 

[sunny_talwar]

What is the expected output needs to look like?

[lalitkgehlot89]

in attached qvw expected result at ROWNO 11 is ok, result at ROWNO 36 is also ok, but result at ROWNO 45 is wrong.

at ROWNO 45 result should be average of Tranistloss ROWNO 44 to 37 and 35 to 34 ie.--0.0190121007571395

same condition will be applied in rest blank cells. only cell with value will be considered.

[sunny_talwar]

There might be a better way to do this... but this seems to work

Test:
LOAD Rake_No, 
     Unloading_Date, 
     RR_Weight, 
     Gross_Weight, 
     Tare_Weight,
     Gross_Weight - Tare_Weight as ActualWeight,
     (RR_Weight - (Gross_Weight - Tare_Weight))/RR_Weight as Transitloss
//     (RR_Weight - (Gross_Weight - Tare_Weight))/RR_Weight as TransitLoss_Temp  
FROM Data.xlsx
(ooxml, embedded labels, table is Sheet1);

Test1:
LOAD Rake_No, 
     Unloading_Date, 
     RR_Weight, 
     Gross_Weight, 
     Tare_Weight,
     ActualWeight,
     Transitloss,
     RowNo() AS ROWNO
Resident Test
Order By Rake_No,Unloading_Date;

DROP Table Test;

For i = 0 to 100

	Left Join (Test1)
	LOAD ROWNO + $(i) as ROWNO,
		 Transitloss as Transitloss_$(i)
	Resident Test1;

NEXT

CrossTableTest:
CrossTable(Header, Value, 8)
LOAD *
Resident Test1;

DROP Table Test1;

FinalTable:
LOAD Rake_No, 
     Unloading_Date, 
     RR_Weight, 
     Gross_Weight, 
     Tare_Weight,
     ActualWeight,
     Transitloss,
     ROWNO,
     Avg(Value) as TransitLoss_Temp
Group By Rake_No, Unloading_Date, RR_Weight, Gross_Weight, Tare_Weight, ActualWeight, Transitloss, ROWNO;
LOAD Rake_No, 
     Unloading_Date, 
     RR_Weight, 
     Gross_Weight, 
     Tare_Weight,
     ActualWeight,
     Transitloss,
     ROWNO,
     Header,
     Value
Resident CrossTableTest
Where AutoNumber(Header, ROWNO) <=10;

DROP Table CrossTableTest;

[Gysbert_Wassenaar]

See attached qvw for another solution.