How to inspect N previous records in load script

[#1]

I have a dilemma with a load script. I have not been able to find a way to conditionally set the value of a flag based on a related set of data and am hoping someone can provide a solution.

Here's my scenario: I am loading a set of test results that includes an ID (which defines the identify of the person & test they took), the year in which the user tested, the status of the test (Pass or Fail), and the score achieved on the test. My goal is to set a flag indicating each user's best attempt per year on each test. The rules for setting this flag are:

1. Until a user passes, set the flag for the highest score in a given year.  If there are multiple attempts with identical scores in the same year,  set the flag on the first attempt (i.e., earliest test date that year).
2. Once a user passes a test, stop setting the flag for any subsequent tests.

Here is a sample set of data which includes the desired value for the flag on each record:

ID	Test Date	Year	Status	Score	Flag
123	1/1/08	2008	F	100	Y
123	1/1/09	2009	F	105	Y
123	1/1/10	2010	F	130	Y
123	1/1/11	2011	P	150	Y
234	1/1/08	2008	F	105	N
234	3/1/08	2008	F	135	Y
234	1/1/09	2009	P	155	Y
345	1/1/09	2009	F	145	N
345	6/1/09	2009	P	155	Y
345	1/1/10	2010	F	100	N
456	1/1/08	2008	F	95	Y
456	1/1/10	2010	F	110	Y
456	1/1/11	2011	F	125	Y

The way I am handling this now is I make two passes through the data. In the first pass, I sort by ID, then Test Date and set an interim flag with this code:

if(Status = 'P' and exists('PassAnnualAttempt', ID), 0, if(Status = 'P', ID, 0)) as PassAnnualAttempt

This works reliably to set my flag for the first passing attempt.

Then I go through the data again, this time sorting by ID desc, Year desc, Status desc, Score desc, Test Date asc. I then use this code to set a second interim flag:

if((ID <> Previous(ID) or Year <> Previous(Year)) AND Status = 'F', ID, 0) as NoPassAnnualAttempt

This unconditionally (i.e., without regard to the value of PassAnnualAttempt for this ID) sets a flag for rule #1 above.

Finally, I interpret both interim flags to set the final value of "Flag" using this code:

if(PassAnnualAttempt = ID, 'Y', if(NoPassAnnualAttempt = ID, 'Y', 'N')) as Flag

The problem I am having is that this code does not handle rule #2 properly. If a user passes a test, then retakes it later and fails, I am ending up with both records having a Flag value of "Y", when I should never set the vaue to "Y" after an initial Pass. I have tried using a LOOKUP function (which seemed to crash with memory issues) and an EXISTS function when calculating the second and final flags, without success.

What I want to do is to be able to iterate through a set of records (e.g., all records for ID 345 above) and inspect the value of PassAnnualAttempt so that I will not set the Flag to "Y" if any other of that ID's records have PassAnnualAttempt = "Y" (besides the first Pass).

Does anyone have any ideas for this? Can I do this within QlikView or should I try to precalculate the data before loading it?

Thanks,

Jim

[#2]

You need to use the peek & previous function with multiple resident load to handel both the rules.

[#3]

I had looked into peek/previous, but I'm not sure that would be reliable. A person can take an unlimited number of tests and the way I understand peek/previous, you need to define how many records you are looking back at.

So if I ran multiple resident loads where I issue commands something like these:

     peek( 'ID', 1), peek( 'ID', 2), peek( 'ID', 3), peek( 'ID', 4)  - (not sure if that's the right syntax, but I think you're suggesting something like that)

I would not handle those users with 5 or more records.

What I need is something that dynamically defines the record set I'm working on within the table, something like this SQL statement:

UPDATE recordset

SET NoPassAnnualAttempt = 'Y'

WHERE ID = @ID

AND ID NOT IN (SELECT ID FROM recordset WHERE PassAnnualAttempt = 'Y')

AND Status = 'F'

ORDER BY ID desc, Year desc, Status desc, Score desc, Test Date asc

(That's not exactly it, because I have to only get the first one each year, but hopefully you get the point.)

[mike_garcia]

For what I see in your QlikView code, the only thing you need to change is the Exists Function parameters. You have 'PassAnnualAttempt' (with single quotes), when you should have PassAnnualAttempt (without single quotes) so than QlikView evaluates the Values in that field otherwise it is just comparing a string to the ID Field value.

Give it a try:

• if(Status = 'P' and exists(PassAnnualAttempt, ID), 0, if(Status = 'P', ID, 0)) as PassAnnualAttempt

Regards,

Mike.

[#5 · Accepted solution]

Try something like this,

if((ID<>peek('ID') OR Year<>peek('Year')) AND [Status]='P','Y',

       if((ID<>peek('ID') OR Year<>peek('Year')) AND [Status]='F','N',

             if(ID=peek('ID') AND Year=peek('Year') AND (peek('Status')='P' OR peek('Flag')='Already Passed','Already Passed'),'Already Passed',

                  if(ID=peek('ID') AND Year=peek('Year') AND peek('Status')='F' AND [Status]='P','Y')))) as 'Flag'

For the first row of an ID & Year, it puts 'Y' for P and 'N' for F

For the second row on, it looks at the previous row and puts 'Already Passed' if the first row passed, 'Y' if the previous row did not pass and the [Status]='Y', and 'N' if the previous row did not pass and the [Status]='F'

I'm sure I missed syntax somewhere but hopefully you get the idea.

[#6]

Thanks, Miguel. That was actually a typo - my code doesn't use the single quotes.

I suppose my question on EXISTS boils down to whether or not it operates on all of the records in the resident table or only those that have been loaded so far. Does anybody know which way EXISTS works?

[#7]

Thanks, Trent. This approach seems to be working.

Cheers,

Jim