4 Replies Latest reply: May 18, 2011 9:04 AM by Filippo Tabusso RSS

    intervalmatch duplication of rows

      Hi everybody,

       

      here is an extremely simplified version of my script:

       

      agende:
      LOAD * INLINE [
          COD_EROG, DATAJ_INIZIO, DATAJ_FINE
          E1, 03/01/2011, 10/01/2011
          E2, 03/01/2011, 10/01/2011    
      ];
      
      
      calendar:
      LOAD * INLINE [
                DAY
                07/01/2011
      ];
      
      
      match:
      left join(agende)
      IntervalMatch (DAY) LOAD DATAJ_INIZIO, DATAJ_FINE RESIDENT agende;
      
      

       

      The problem is that doing this way agenda rows are duplicated. I have already read other post discussing alternative ways to do the same job and to avoid duplications. But I still haven't understood why rows are duplicated (sorry, maybe it is simple, forgive me if I'm a little dumb...).

       

      Can someone explain me why intervalmatch creates the duplication (or point me to an already made explanation)?

       

      Thanks in advance,

       

      Fil

        • intervalmatch duplication of rows
          Erich Shiino

          Hi, Fil

          I believe the duplication of your records is expected because the date 7/01 is in the middle of the line with E1 and E2. The date on calendar is associated with both intervals because they are the same.

          I don't know what your data mean but the intervals should not overlap if you don't want duplicates.

           

           

          Hope it helps,

           

          Erich

            • intervalmatch duplication of rows

              Hi Erich,

               

              I would have expected to get a table like:

               

              COD_EROG     DATAJ_INIZIO     DATAJ_FINE     DAY

              E1                       03/01/2011           10/01/2011         07/01/2011

              E2                       03/01/2011           10/01/2011         07/01/2011

               

              while I get:

               

              COD_EROG     DATAJ_INIZIO     DATAJ_FINE     DAY

              E1                       03/01/2011           10/01/2011         07/01/2011

              E1                       03/01/2011           10/01/2011         07/01/2011

              E2                       03/01/2011           10/01/2011         07/01/2011

              E2                       03/01/2011           10/01/2011         07/01/2011

               

              Why is like that?

               

              Thanks

                • intervalmatch duplication of rows
                  Erich Shiino

                  hI,

                   

                  I used this now:

                  agende:

                  LOAD * INLINE [

                      COD_EROG, DATAJ_INIZIO, DATAJ_FINE

                      E1, 03/01/2011, 10/01/2011

                      E2, 03/01/2011, 10/01/2011   

                  ];

                   

                   

                  calendar:

                  LOAD * INLINE [

                            DAY

                            07/01/2011

                  ];

                   

                   

                  match:

                  left join(agende)

                  IntervalMatch (DAY) LOAD DISTINCT  DATAJ_INIZIO, DATAJ_FINE RESIDENT agende;

                   

                  I included distinct because during interval match cod_erog was not considered. Your date was associated with the same interval and you got two lines. Then when you use the left join (with two identical lines) you duplicated it again and get two for each code.

                   

                  Hope it helps,

                   

                  Erich