Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: We have made updates to resolve a Page Not Found error. If you still get this error, please let us know in the Community Corner.
Highlighted
lalitkgehlot89
Contributor II

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

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 

    

Labels (1)
4 Replies

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

What is the expected output needs to look like?
lalitkgehlot89
Contributor II

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

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.

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

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;
MVP & Luminary
MVP & Luminary

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

See attached qvw for another solution. 


talk is cheap, supply exceeds demand