Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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 |
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;