Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nsnybs21qv
New Contributor II

Script Help needed

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 IDStudent IDScore 1Score 2Score 3Period
Project AStudent 1ABBCurrent
Project AStudent 2CCBCurrent
Project BStudent 1ADDCurrent
Project BStudent 2BCCCurrent
Project BStudent 3DDBCurrent
Project AStudent 1ABBPrior
Project AStudent 2CDBPrior
Project BStudent 1ADDPrior
Project BStudent 2CCCPrior
Project BStudent 3DDA

Prior

  The script should return  

Project AStudent 2
Project BStudent 2
Project BStudent 3
1 Solution

Accepted Solutions

Re: Script Help needed

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;


Capture.PNG

2 Replies
MVP
MVP

Re: Script Help needed

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 AStudent 21
Project BStudent 21
Project BStudent 31


Re: Script Help needed

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;


Capture.PNG

Community Browser