Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
using QV11 looking to ADD a column via load script of the
Maximum of the Grade for each student
for a period of 3 previous observation .
so for example Dan on 16/01/2016 looking far max of
25/02/2008 100
01/01/2016 100
06/01/2016 120
so 120
Thanks,
Raphael
LOAD * INLINE [
Date, Name, Grade
20/02/2008, Dan, 80
20/02/2008, John, 8
25/02/2008, Dan, 100
25/02/2008, John, 10
01/01/2016, Dan, 100
01/01/2016, John, 2
06/01/2016, Dan, 120
06/01/2016, John, 4
16/01/2016, Dan, 90
16/01/2016, John, 6
];
Not sure I understand why this is 100?
Do you may be want this?
Table:
LOAD * INLINE [
Date, Name, Grade
20/02/2008, Dan, 80
20/02/2008, John, 8
25/02/2008, Dan, 100
25/02/2008, John, 10
01/01/2016, Dan, 100
01/01/2016, John, 2
06/01/2016, Dan, 120
06/01/2016, John, 4
16/01/2016, Dan, 90
16/01/2016, John, 6
];
FinalTable:
LOAD *,
If(Name = Peek('Name', -1) and Name = Peek('Name', -2), RangeMax(Grade, Peek('Grade', -1), Peek('Grade', -2)),
If(Name = Peek('Name', -1), RangeMax(Grade, Peek('Grade', -1)), Grade)) as Max_3_Before
Resident Table
Order By Name, Date;
DROP Table Table;
looking for max 3 above.
so
Make sense, but how about these two?
you right I am wrong in my example, sorry
May be this
Table:
LOAD * INLINE [
Date, Name, Grade
20/02/2008, Dan, 80
20/02/2008, John, 8
25/02/2008, Dan, 100
25/02/2008, John, 10
01/01/2016, Dan, 100
01/01/2016, John, 2
06/01/2016, Dan, 120
06/01/2016, John, 4
16/01/2016, Dan, 90
16/01/2016, John, 6
];
FinalTable:
LOAD *,
If(Name = Peek('Name', -1) and Name = Peek('Name', -2) and Name = Peek('Name', -3), RangeMax(Peek('Grade', -1), Peek('Grade', -2), Peek('Grade', -3)),
If(Name = Peek('Name', -1) and Name = Peek('Name', -2), RangeMax(Peek('Grade', -1), Peek('Grade', -2)),
If(Name = Peek('Name', -1), RangeMax(Peek('Grade', -1))))) as Max_3_Before
Resident Table
Order By Name, Date;
DROP Table Table;
that is grand, but my actual requirement is 100 or even more before... so don't think will be able to manually add all the peeks ...
If(Name = Peek('Name', -1) and Name = Peek('Name', -2) and Name = Peek('Name', -3), RangeMax(Peek('Grade', -1), Peek('Grade', -2), Peek('Grade', -3)),
If(Name = Peek('Name', -1) and Name = Peek('Name', -2), RangeMax(Peek('Grade', -1), Peek('Grade', -2)),
If(Name = Peek('Name', -1), RangeMax(Peek('Grade', -1))))) as Max_3_Before
May be use The As-Of Table in that case