Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

max n previous

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 .

23-04-2018 11-23-04.png

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

9 Replies
sunny_talwar

Not sure I understand why this is 100?

Capture.PNG

sunny_talwar

Do you may be want this?

Capture.PNG

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;

Anonymous
Not applicable
Author

looking for max 3 above.

so 23-04-2018 11-23-04.png

sunny_talwar

Make sense, but how about these two?

Capture.PNG

Anonymous
Not applicable
Author

23-04-2018 11-23-04.png

Anonymous
Not applicable
Author

you right I am wrong in my example, sorry

sunny_talwar

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;

Capture.PNG

Anonymous
Not applicable
Author

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

sunny_talwar

May be use The As-Of Table in that case