2 Replies Latest reply: Mar 9, 2016 11:55 AM by Sunny Talwar RSS

    Script Help needed

    Yvonne Han

      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
        • Re: Script Help needed
          Stefan Wühl

          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
            Sunny Talwar

            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