Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
r20017aaa
Creator III
Creator III

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;

r20017aaa
Creator III
Creator III
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

r20017aaa
Creator III
Creator III
Author

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

r20017aaa
Creator III
Creator III
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

r20017aaa
Creator III
Creator III
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