topic Re: Problem with grouped table Qlik sense script in App Development

Problem with grouped table Qlik sense script

josemaria_cabre — Tue, 23 Jan 2024 20:52:53 GMT

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.

Re: Problem with grouped table Qlik sense script

rwunderlich — Tue, 23 Jan 2024 01:25:48 GMT

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

Re: Problem with grouped table Qlik sense script

josemaria_cabre — Tue, 23 Jan 2024 20:51:31 GMT

It worked great, thank you so much Rob!

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