5 Replies Latest reply: Feb 6, 2013 7:59 AM by Jatish Patel RSS

    FirstSortedValue to define occurrence

    Jatish Patel

      Hi all,

       

      I am creating a demo application to demonstrate changes in patient care from admission to discharge. The patient can attend more than once so I am trying to give each row of data an occurrence ie.

       

      Row_IDPatient_IDAdmission_dateOccur
      11104/01/20131
      22204/01/20131
      33304/01/20131
      41103/02/20132
      53304/02/20132
      64405/02/20131
      75506/02/20131
      84407/02/20132
      91108/02/20133
      102205/03/20132

       

      I attempted to use firstsortedvalue.

       

      LOAD Patient_ID, 
          if(FirstSortedValue(Patient_ID,Admission_date,1)<>null(),1) as Occur
      FROM Data_sample.csv
      GROUP BY Patient_ID;
      
      LOAD Patient_ID, 
          if(FirstSortedValue(Patient_ID,Admission_date,2)<>null(),2) as Occur
      FROM Data_sample.csv
      GROUP BY Patient_ID;
      
      LOAD Patient_ID, 
          if(FirstSortedValue(Patient_ID,Admission_date,3)<>null(),3) as Occur
      FROM Data_sample.csv
      GROUP BY Patient_ID;
      

       

      This works but it creates a separate table. I tried using Row_ID, in the above loads but I come up with invalid expression. I would prefer to join this to the main table.

       

      Ideally I would like to be able to produce a chart like this, with the dimension of the graph being correctly sorted.qv_app.jpg

       

      I have attached my sample qlikview and data file, any help would be much appreciated.

       

      Many thanks

      Jat