5 Replies Latest reply: Oct 4, 2017 7:49 AM by Sunny Talwar RSS

    Joining on condition

    Arpit Kharkia

      Hi all,

       

      I have the following two tables:

       

      TABLE A-

       

      MemDate
      12320/01/2016
      12325/01/2017

       

       

      TABLE B-

       

      MemDate

      123

      15/5/2016
      12316/7/2016
      123

      17/7/2016

      12301/01/2017
      12314/02/2017
      12320/02/2017

       

      I need to add a column in table B based on table A. Final table B should look like:

       

      MemDateMemKey

      123

      15/5/201612316
      12316/7/201612316
      123

      17/7/2016

      12316
      12301/01/201712316
      12314/02/201712317
      12320/02/201712317

       

       

      My logic is that if date in table A is less than that of B. Then those dates hase to be mapped to that date until a greater date is encountered. in other words my dates in B has to be mapped to that of A if table B date is greater than that of A.

        • Re: Joining on condition
          Sunny Talwar

          May be this

           

          TableATemp:

          LOAD * INLINE [

              Mem, Date

              123, 20/01/2016

              123, 25/01/2017

          ];

           

          TableA:

          LOAD Mem,

          Date as Start,

          Mem & Right(Year(Date), 2) as MemKey,

          Date(If(Mem = Previous(Mem), RangeSum(Previous(Date), -1), Today())) as End

          Resident TableATemp

          Order By Mem, Date desc;

           

          DROP Table TableATemp;

           

          TableB:

          NoConcatenate

          LOAD * INLINE [

              Mem, Date

              123, 15/5/2016

              123, 16/7/2016

              123, 17/7/2016

              123, 01/01/2017

              123, 14/02/2017

              123, 20/02/2017

          ];

           

          Left Join (TableB)

          IntervalMatch(Date, Mem)

          LOAD Start,

          End,

          Mem

          Resident TableA;

           

          Left Join (TableB)

          LOAD *

          Resident TableA;

           

          DROP Table TableA;

           

          Capture.PNG

          • Re: Joining on condition
            kushal chawda

            Data:

            LOAD

                 Mem,

                 MinDate,

                 MaxDate,

                 date(MinDate+IterNo()-1) as Date,

                 1 as Flag

            While MinDate+IterNo()-1<=MaxDate;

            LOAD Mem,

                 date(min(Date)) as MinDate,

                 date(max(Date)-1) as MaxDate

            Group by Mem;

            LOAD * INLINE [

                Mem, Date

                123, 20/01/2016

                123, 25/01/2017

            ];

             

            right Join(Data)

            LOAD * INLINE [

                Mem, Date

                123, 15/5/2016

                123, 16/7/2016

                123, 17/7/2016

                123, 01/01/2017

                123, 14/02/2017

                123, 20/02/2017

            ];

             

            Left Join(Data)

            LOAD Distinct Mem,

                 Mem&date(max(MinDate),'YY') as MinMemKey,

                 Mem&date(max(MaxDate),'YY') as MaxMemKey

            Resident Data

            Group by Mem;

                      

            Final:

            NoConcatenate

            LOAD Mem,

                 Date,

                 if(Flag=1,MinMemKey,MaxMemKey) as MemKey

            Resident Data;

             

            DROP Table Data;

            • Re: Joining on condition
              Eduardo DImperio

              Maybe This:

               

              TableA:

              Load

              Mem,

              Date AS Date_A,

              Mem & Right(Year(Date), 2) as MemKey

              From tableA;

               

              Left Join( TableA)

              //TableB

              Load

              *

              From TableB

               

              Final:

               

              Load

              Mem,

              Date,

              If(Date>Date_A,Max(MemKey),Min(MemKey)) AS MemKey

              Resident Table_A

              Group by

              Mem,

              Date,

              Date_A

               

              Drop Table Table_A;

              • Re: Joining on condition
                Arpit Kharkia

                Thnaks for all the answers!