topic Re: max n previous in QlikView

max n previous

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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
];

Re: max n previous

sunny_talwar — Mon, 23 Apr 2018 16:14:10 GMT

Not sure I understand why this is 100?

Re: max n previous

sunny_talwar — Mon, 23 Apr 2018 16:14:57 GMT

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;

Re: max n previous

Anonymous — Mon, 23 Apr 2018 16:17:38 GMT

looking for max 3 above.

Re: max n previous

sunny_talwar — Mon, 23 Apr 2018 16:20:23 GMT

Make sense, but how about these two?

Re: max n previous

Anonymous — Mon, 23 Apr 2018 16:21:09 GMT

Re: max n previous

Anonymous — Mon, 23 Apr 2018 16:23:21 GMT

you right I am wrong in my example, sorry

Re: max n previous

sunny_talwar — Mon, 23 Apr 2018 16:23:46 GMT

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;

Re: max n previous

Anonymous — Mon, 23 Apr 2018 16:27:15 GMT

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

Re: max n previous

sunny_talwar — Mon, 23 Apr 2018 16:31:20 GMT

May be use The As-Of Table in that case