1 Reply Latest reply: Oct 31, 2015 11:45 AM by Gysbert Wassenaar RSS

    Advanced Concatenate Based on Date

      So I have two inline tables. I want to create a table which concatenate these two only if the date field of the first one is one month before the date field of last one. Example:

       

      Main:

      LOAD * INLINE [

          Key, DATE, Qty

          1, 10/1/2015, 100

          2, 10/1/2015, 200

          3, 10/1/2015, 300

          4, 10/1/2015, 400

          5, 10/1/2015, 500

      ];

       

       

      NoConcatenate

      temp_table:

      LOAD * INLINE [

          Key, DATE2, Qty

          1, 11/1/2015, 10

          2, 11/1/2015, 20

          6, 11/1/2015, 30

          7, 11/1/2015, 40

          8, 11/1/2015, 50

          9, 12/1/2015, 60

      ];

       

      temp:

      LOAD

      Key as KeyInTemp,

      DATE as DATEInTemp

      Resident Main;

       

      Concatenate(temp)

      LOAD

      Key as KeyInTemp

      Resident temp_table Where Date(DATE#(DATEInTemp,'MM/DD/YYYY')) = AddMonths(Date(DATE#(DATE2,'MM/DD/YYYY')),-1);

       

      MyResult:

      LOAD Distinct

      *

      Resident temp;

       

      So the expected output is:

      Key     DATEInTemp

      1          10/1/2015

      2          10/1/2015

      3          10/1/2015

      4          10/1/2015

      5          10/1/2015

      6          10/1/2015

      7          10/1/2015

      8          10/1/2015

       

      ***9 will not be added because date is two month ahead***

       

      Thanks in advance guys

        • Re: Advanced Concatenate Based on Date
          Gysbert Wassenaar

          Maybe like this:

           

          Main:

          LOAD * INLINE [

              Key, DATE, Qty

              1, 10/1/2015, 100

              2, 10/1/2015, 200

              3, 10/1/2015, 300

              4, 10/1/2015, 400

              5, 10/1/2015, 500

          ];

           

          LET vDate = num(date#(peek('DATE'),'M/D/YYYY'));

           

          LOAD Key, DATE2 as DATE, Qty INLINE [

              Key, DATE2, Qty

              1, 11/1/2015, 10

              2, 11/1/2015, 20

              6, 11/1/2015, 30

              7, 11/1/2015, 40

              8, 11/1/2015, 50

              9, 12/1/2015, 60

          ]

          WHERE num(AddMonths(Date#(DATE2,'M/D/YYYY'),-1)) = $(vDate);

          ;