Problem with grouped table Qlik sense script

[josemaria_cabre]

Hi,

Given this example table:

LOAD * inline [
StudentCode|Module|Result
54582|1|success
54582|2|success
54582|3|pending
54582|4|pending
54582|5|pending
11125|1|success
11125|2|failed
11125|3|success
11125|4|pending
] (delimiter is '|');

I'd like to obtain a table, where:

- LatestModule is the latest Module succeeded by student
- TotalsucceededModules is a count of succeeded modules by student.

**Succeeded modules must be consecutive, so If Student succeeded e.g. module 1 and 3, but module 2 is either "failed" or "pending", latest succeeded module must be 1.

StudentCode	LatestModule	TotalsucceededModules
54582	2	2
11125	1	2

I've tried doing this in my script, combining peek and previous functions, and then grouping by Student but I always get aggregation errors.

Can anyone help with this script, pls?

Best regards, 
J.

[rwunderlich · Accepted solution]

How about something like:

Raw:
LOAD * inline [
StudentCode|Module|Result
54582|1|success
54582|2|success
54582|3|pending
54582|4|pending
54582|5|pending
11125|1|success
11125|2|failed
11125|3|success
11125|4|pending
] (delimiter is '|');

Score:
LOAD
  StudentCode,
  TotalSucceededModules,
  Len(SubField(Score, 'N', 1)) as LatestModule
;
LOAD
  StudentCode,
  Concat(if(Result = 'success', 'Y', 'N'), '', Module) as Score,
  -Sum(Result = 'success') as TotalSucceededModules
Resident Raw
Group By StudentCode
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[josemaria_cabre]

It worked great, thank you so much Rob!

I would never have solved it like that. Absolutely brilliant.