5 Replies Latest reply: Sep 8, 2015 4:19 PM by Arturo Tagle RSS

    conditional row merge, can it be done?

      Hi everybody,

       

      I have this problem and i can´t find a solucion. I have a table like this one:

       

      AnimalNameDiagnosesVisit DateNext visit
      DOGRockycough12-oct22-oct
      DOGRockycough14-oct17-oct
      DOGRockycough15-nov20-nov
      DOGRockyrash18-nov19-nov
      DOGDukerash20-nov25-nov
      DOGDukerash26-nov28-nov
      DOGDukecough27-nov30-nov

       

      And I need to MERGE or JOIN the rows that are the same animal, name and diagnosis within in a short period of time (maybe two days) and get the first visit and last visit

      For example, for the first two rows I can assume that is the same illness, so I can merge both and get the first and max date, in this case, the first row dates. But the third row is a new one even though it has the same illness, but is a month later.

      And for the dog duke, it came back with a rash a the next day of his last visit, so I can assume that is the same illness, so is the first visit date (fifth row) and the last date on the sixth row.

      The result should be something like this

       

      AnimalNameDiagnosesStartEnd
      DOGRockycough12-oct22-oct
      DOGRockycough15-nov20-nov
      DOGRockyrash18-nov19-nov
      DOGDukerash20-nov28-nov
      DOGDukecough27-nov30-nov

       

      The first table is in order, so there is no need to check the whole table looking for rows to join.

       

      I hope i made myself clear and I thank you all for your time. Any question i'll try to answer it as fast as i can

        • Re: conditional row merge, can it be done?
          Massimo Grossi

          pseudocode

           

          1) make a new table (load resident) in this way

           

          - load the table ordered by Animal, ........Next visit

           

          - if the Animal, Name, Diagnoses of current row are the same of prevous row

          (if(peek(Animal)=Animal and .......)

           

               check (always using peek or previous to get the values of the field in previous record) date difference

               calculate the 2 dates (2 new fields)

               flag the record

           

          2) read the flagged records of the new table (use the 2 new date fields)

            • Re: conditional row merge, can it be done?

              Thank you, That's a very good starting point. I didn't know about peek and previous so let's give it a try

              • Re: conditional row merge, can it be done?

                Thank you again for your guidance. I work it out and got this solution

                 

                Base_TB:

                Load Animal,

                       Name,

                       Diagnoses,

                       ´Visit Date´,

                       ´Observation days´,

                       RowNo()as ID,

                       if( Name=previous(Name) and

                           Diagnoses=previous(Diagnoses) and

                           [Visit Date] <= (previous([Observation days])+2),     peek(ID),     RowNo()) as Flag;

                LOAD * INLINE [

                Animal,Name,Diagnoses,Visit Date,Observation days

                DOG,Rocky,cough,12-10-2015,22-10-2015

                DOG,Rocky,cough,14-10-2015,17-10-2015

                DOG,Rocky,cough,15-11-2015,20-11-2015

                DOG,Rocky,rash,18-11-2015,19-11-2015

                DOG,Duke,rash,20-11-2015,25-11-2015

                DOG,Duke,rash,26-11-2015,28-11-2015

                DOG,Duke,cough,27-11-2015,30-11-2015

                ];

                 

                Final:

                NoConcatenate

                Load Animal,

                       Name,

                       Diagnoses,

                       date(min(´Visit Date´)) as Min_Date,

                       date(max(´Observation days´)) as max_Date,

                       Flag

                resident Base_TB Group by Flag,Animal,Name,Diagnoses;