5 Replies Latest reply: Nov 5, 2016 8:06 PM by Marco Wedel RSS

    Numbering events by time

    Daniel Nasseh

      Hello,

       

      once again got a really hard nut to crack:

       

      Let's assume we have students. These students have Exam Attempts.

       

      So we have a table like this:

       

      StudentExams (Attempt)Date
      Student1Math19.10.2015
      Student1Math24.10.2015
      Student1Math30.10.2015
      Student2Math20.10.2015
      Student3Math19.10.2015
      Student3Math28.10.2015

       

      Now what we would like to do is change the Text Math to Attempt1, Attempt2, Attempt3 etc.

       

      So the final table should change like the following:

       

       

      StudentExams (Attempt)Date
      Student1Attempt 119.10.2015
      Student1Attempt 224.10.2015
      Student1Attempt 330.10.2015
      Student2Attempt 120.10.2015
      Student3Attempt 119.10.2015
      Student3Attempt 228.10.2015

       

      The dates of the exams are random. So it means that a math test for one student can be on another date for another student.

       

      So at the moment we are pretty clueless how to procede. Maybe you can give us a hint about a good function we could use or in general how to progress.

        • Re: Numbering events by time
          Tresesco B

          Try like:

          Load

                    Student,

                    'Attempt' & AutoNumber(RowNo(), Student) as  Exam(Attempt),

                    Date

          From <>;

            • Re: Numbering events by time
              Daniel Nasseh

              Thanks! Works like a charm!

              • Re: Numbering events by time
                Daniel Nasseh

                Dear Tresesco Biswas,

                 

                it seems to work if we only have one kind of Exam(like Math). But if we have multiple it does not work anymore:

                 

                Here is our current code:

                 

                Events:

                Load

                          Student,

                          if(Exams='Math','Math' & AutoNumber(RowNo(),Student),

                          ) as Events,

                          Date

                          resident Datatable

                          WHERE Exams='Math';

                          ;

                         

                CONCATENATE

                 

                Load

                          Student,

                          if(Exams='Biology','Biology' & AutoNumber(RowNo(),Student),

                          ) as Events,

                          Date

                          resident Datatable

                          WHERE Exams='Biology';

                          ;

                 

                CONCATENATE

                 

                Load

                          Student,

                          if(Exams='Chemistry','Chemistry' & AutoNumber(RowNo(),Student),

                          ) as Events,

                          Date

                          resident Datatable

                          WHERE Exams='Chemistry';

                          ;

                 

                CONCATENATE

                 

                Load

                          Student,

                          if(Exams='Physics','Physics' & AutoNumber(RowNo(),Student),

                          ) as Events,

                          Date

                          resident Datatable

                          WHERE Exams='Physics';

                 

                The problem is - if we have a student and he has only three Math courses then he has

                Math1

                Math2

                Math3

                 

                That is correct.

                 

                But if he has 3 Math courses and 2 Biology courses he has:

                Math1

                Biology2

                Math3

                Math4

                Biology5

                 

                Correct would be:

                Math1

                Biology1

                Math2

                Math3

                Biology2

                 

                So we dont know exactly what to do - but one idea would be to substract the amount of Math courses for a student from the Atuonumber for Biology... something like this:

                 

                Load

                          Student,

                          if(Exams='Biology','Biology' & (AutoNumber(RowNo(),Student)-Count(Math???),

                          ) as Events,

                          Date

                          resident Datatable

                          WHERE Exams='Biology';

                          ;

                 

                Unfortunately we dont know if this is the right way and we do not know how to formulate that subquery (if this would be the right way to approach the problem).

                 

                So any help apreciated!

              • Re: Numbering events by time
                Marco Wedel

                sample data

                 

                StudentExams (Attempt)Date
                Student1Math19.10.2015
                Student1Math24.10.2015
                Student1Biology30.10.2015
                Student2Math20.10.2015
                Student3Math19.10.2015
                Student3Math28.10.2015
                Student1Physics15.10.2015
                Student3Biology19.10.2015
                Student2Physics25.10.2015
                Student1Math27.10.2015
                Student3Physics23.10.2015
                Student4Math05.10.2015
                Student1Biology09.10.2015
                Student3Physics12.10.2015
                Student4Math01.10.2015
                Student1Physics03.10.2015
                Student4Physics07.10.2015
                Student4Math26.10.2015
                Student1Biology24.10.2015
                Student4Physics17.10.2015
                • Re: Numbering events by time
                  Marco Wedel

                  Hi,

                   

                  the trick is to use a combination of student and exam as second Autonumber() parameter (AutoID), because you're counting per student and exam instead of per student only. So one example extending Tresesco's solution might be:

                   

                  QlikCommunity_Thread_238549_Pic1.JPG

                   

                   

                  tabExams:
                  LOAD RecNo() as ID, *   
                  FROM [https://community.qlik.com/thread/238549] (html, codepage is 1252, embedded labels, table is @3);
                  
                  Left Join (tabExams)
                  LOAD ID,
                      [Exams (Attempt)]&AutoNumber(ID,Student&'/'&[Exams (Attempt)]) as Events
                  Resident tabExams
                  Order By Date;
                  

                   

                  hope this helps

                   

                  regards

                   

                  Marco