5 Replies Latest reply: Oct 15, 2016 10:42 AM by Michael Solomovich RSS

    How to find new or resheduled

    scotly victor

      How Can I find a person appointment new or rescheduled ?

       

      pid      date

      7344  01/02/2016

      7344  01/03/2016

      7344  04/03/2016

      4555  04/02/2016

      4555   06/03/2016

       

      Desired Output is

       

      pid      type               date

      7344  new               01/02/2016

      7344  rescheduled   01/03/2016

      7344  rescheduled   04/03/2016

      4555   new              04/02/2016

      4555   reshdeduled  06/03/2016

        • Re: How to find new or resheduled
          Anand Chouhan

          Try this in the load script

           

           

          Data:
          LOAD * Inline
          [
          pid,      date
          7344,  01/02/2016
          7344,  01/03/2016
          7344,  04/03/2016
          4555,  04/02/2016
          4555,  06/03/2016
          ]
          ;

          NoConcatenate
          LOAD
          *,
          If(pid = Previous(pid),'rescheduled','new') as New
          Resident Data;
          DROP Table Data;

           

          Output you Get

             

          piddateNew
          734401/02/2016new
          734401/03/2016rescheduled
          734404/03/2016rescheduled
          455504/02/2016new
          455506/03/2016rescheduled

           

          Regards,

          Anand

          • Re: How to find new or resheduled
            Tereza Gregorova

            Try the following:

             

            Table1:

            LOAD * Inline
            [
            pid,      date
            7344,  01/02/2016
            7344,  01/03/2016
            7344,  04/03/2016
            4555,  04/02/2016
            4555,  06/03/2016
            ]
            ;

            NewTable:

            Load*,

            if(pid = peek('pid',-1),'rescheduled','new') as appointment_type

            Resident Table1;


            Drop table Table1;


            It should not take too much longer to load. It would, if you would not do the Drop table statement as it would start to create synthetic key. But as you are dropping the Table1 and keeping only the NewTable, this should be fairly quick load.



            • Re: How to find new or resheduled
              Michael Solomovich

              I prefer to use mapping for everything

               

              Data:

              LOAD * Inline

              [

              pid, date

              7344, 01/02/2016

              7344, 01/03/2016

              7344, 04/03/2016

              4555, 04/02/2016

              4555, 06/03/2016

              ];

               

              DateMap:

              MAPPING LOAD DISTINCT

                pid as Input,

                date(min(date)) as Output

              RESIDENT Data

              GROUP BY pid;

               

              LEFT JOIN (Data) LOAD

                pid,

                date,

                if(applymap('DateMap',pid)=date, 'new', 'rescheduled') as type

              RESIDENT Data;