The best practice is to prepare this information in your load script, rather than calculate it on-the-fly when you need it. The script below uses your spreadsheet to load a table with the data. It also adds two fields; a test_id that uniquely identifies each test; and a flag called "report" at the end of the record. This flag will be 1 for each question of each test that fits your criteria, otherwise it will be null.
This gives you a lot more flexibility. You can just display the each question or test_id for a user where the report_flag = 1. You also can reduce your data, keeping only the records where report_flag = 1. I think this option is closer to that you want.
The script is below. If you run it you will see that the records you wanted have report_flag = 1. The ones you don't want have report_flag = null. To reduce the data, simply load the table to another table "where report_flag = 1".
I've also attached a spreadsheet with the contents of the Scores table after you run this script.
I hope this solves your issue.
// First load in the spreadsheet.
[RawScores]:
LOAD
[user],
[date],
[time],
[test_type],
[question_order],
[question],
[answer],
[pass_fail]
FROM [lib://Data/Test Example.xlsx] // Change to be your data file to load
(ooxml, embedded labels, table is Sheet1);
//Now, loop through the table you loaded and add a unique test_id for each test.
Let vNumRows = NoOfRows('RawScores');
Let vKey = 0;
Set vConcatenate = 'NoConcatenate';
For i = 0 to $(vNumRows)-1
If Peek('question_order', i, 'RawScores') = 0 then
Let vKey = vKey + 1;
End If
Scores:
$(vConcatenate) LOAD
$(vKey) as test_id,
Peek('user', $(i), 'RawScores') as [user],
Peek('date', $(i), 'RawScores') as [date],
Peek('time', $(i), 'RawScores') as [time],
Peek('test_type', $(i), 'RawScores') as [test_type],
Peek('question_order', $(i), 'RawScores') as [question_order],
Peek('question', $(i), 'RawScores') as [question],
Peek('answer', $(i), 'RawScores') as [answer],
Peek('pass_fail', $(i), 'RawScores') as [pass_fail]
AutoGenerate 1;
Set vConcatenate = 'Concatenate';
Next;
Drop Table [RawScores];
// Identify the pre-test records
[ScoreStatus]:
Load
Max(test_id) as test_id
Resident [Scores]
Where [test_type] = 1
Group by [user];
// Identify the last test that the user passed.
[ScoreStatus]:
Concatenate Load
Max(test_id) as test_id
Resident [Scores]
Where [pass_fail] = 'pass' and [test_type] = 2
Group by [user];
// Identify the first test that the user failed
[ScoreStatus]:
Concatenate Load
Min(test_id) as test_id
Resident [Scores]
Where [pass_fail] = 'fail' and [test_type] = 2
Group by [user];
// Now, add the report_flag to table Scores.
Scores:
Left Join (Scores)
Load
test_id,
1 as report_flag
Resident [ScoreStatus];
Drop Table [ScoreStatus];
