topic Re: Script Help needed in QlikView

Script Help needed

Anonymous — Wed, 09 Mar 2016 16:21:27 GMT

Hi QV expert

I have a student table with students' 3 scores for different projects in 2 periods. see table below.
I want to be able to filter out students with projects that have a score change in the two period for any one of the scores.

Can someone help with the script?

Thanks

Student table: Highlighted are the score change

Project ID	Student ID	Score 1	Score 2	Score 3	Period
Project A	Student 1	A	B	B	Current
Project A	Student 2	C	C	B	Current
Project B	Student 1	A	D	D	Current
Project B	Student 2	B	C	C	Current
Project B	Student 3	D	D	B	Current
Project A	Student 1	A	B	B	Prior
Project A	Student 2	C	D	B	Prior
Project B	Student 1	A	D	D	Prior
Project B	Student 2	C	C	C	Prior
Project B	Student 3	D	D	A	Prior

The script should return

Project A	Student 2
Project B	Student 2
Project B	Student 3

Re: Script Help needed

swuehl — Wed, 09 Mar 2016 16:54:02 GMT

LOAD your data in using a CROSSTABLE LOAD to transform your data into a straight table:

CrossTable (ScoreID, Score, 3)

LOAD [Project ID],[Student ID], Period, [Score 1], [Score 2], [Score 3]

INLINE [

Project ID Student ID Score 1 Score 2 Score 3 Period

Project A Student 1 A B B Current

Project A Student 2 C C B Current

Project B Student 1 A D D Current

Project B Student 2 B C C Current

Project B Student 3 D D B Current

Project A Student 1 A B B Prior

Project A Student 2 C D B Prior

Project B Student 1 A D D Prior

Project B Student 2 C C C Prior

Project B Student 3 D D A Prior

] (delimiter is '\t');

Then create a straight table with two dimensions and one expression:

Project ID	Student ID	Sum( Aggr( If( Score <> If(Rowno() =1, Score,Above(Score)), 1,0), [Project ID], [Student ID], ScoreID, Period))
		3
Project A	Student 2	1
Project B	Student 2	1
Project B	Student 3	1

Re: Script Help needed

sunny_talwar — Wed, 09 Mar 2016 16:55:24 GMT

May be this to create a flags:

Table:

LOAD [Project ID],

[Student ID],

[Score 1],

[Score 2],

[Score 3],

Period

FROM

[https://community.qlik.com/thread/208854]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If([Project ID] = Peek('Project ID') and [Student ID] = Peek('Student ID'),

If(Peek('Score 1') <> [Score 1] or Peek('Score 2') <> [Score 2] or Peek('Score 3') <> [Score 3], 1, 0)) as Flag

Resident Table

Order By [Project ID], [Student ID], Period;

DROP Table Table;