8 Replies Latest reply: Jun 19, 2015 5:45 PM by Marco Wedel RSS

    retrieve the right date

    ferhat oumohand

      Hello All,

      I have two tables:

       

      SENT :

      LOAD *
      inline [
      REPORTING_ID, DATE_APPLI
      2,                       04/05/2015
      ]
      ;


      RECEIVED:
      LOAD *
      inline [
      REPORTING_ID, AS_OF_DATE

      2,                           03/05/2015
      2,                            05/05/2015
      2,                           10/06/2016 ]
      ;

       

      and i have to retrieve  in the table received  the right date, knowing that  the right date is :

      AS_OF_DATE > =DATE_APPLI

      and i must retrieve the Is the closest AS_OF_DATE,  then, in the table RESULT  i  must have :


      Results :

       

      REPORTING_ID, DATE_APPLI, AS_OF_DATE

      2,                   04/05/2015,         05/05/2015

       

      I have not to retrieve 03/05/2015 because 03/05/2015 <  04/05/2015

      I have not to retrieve 10/06/2016 because 05/05/2015  is  the closest

       

      thanks in advance for your help

       

       

        • Re: retrieve the right date
          Sunny Talwar

          Try this script:

           

          SENT :

          LOAD *

          inline [

          REPORTING_ID, DATE_APPLI

          2,                      04/05/2015

          ];

           

          Join (SENT)

          LOAD *

          inline [

          REPORTING_ID, AS_OF_DATE

          2,                          03/05/2015

          2,                            05/05/2015

          2,                          10/06/2016 ];

           

          Join(SENT)

          LOAD *,

            If(AS_OF_DATE - DATE_APPLI > 0, AS_OF_DATE - DATE_APPLI) as Difference

          Resident SENT;

           

          Right Join (SENT)

          LOAD REPORTING_ID,

            Min(Difference) as Difference

          Resident SENT

          Group By REPORTING_ID;

          • Re: retrieve the right date
            Sergey Pokasov

            Hello!

             

            SENT :

            LOAD *

            inline [

            REPORTING_ID, DATE_APPLI

            2,                       04/05/2015

            ];

             

             

            RECEIVED:

            LOAD *

            inline [

            REPORTING_ID, AS_OF_DATE

            2,                           03/05/2015

            2,                            05/05/2015

            2,                           10/06/2016 ];

             

             

             

             

            t1:

            NoConcatenate

            load *, 1 as tp, DATE_APPLI as DT Resident SENT;

            Concatenate (t1)

            LOAD *, 2 as tp, AS_OF_DATE as DT Resident RECEIVED;

             

             

            t2:

            load

              REPORTING_ID,

              DATE_APPLI,

              AS_OF_DATE

            Where checked=1;

            load

              REPORTING_ID, AS_OF_DATE,

              Previous(DATE_APPLI) as DATE_APPLI,

              if(Previous(tp)=1 and Previous(REPORTING_ID)=REPORTING_ID, 1,0) as checked

            Resident t1

            Order By

              REPORTING_ID,

              DT,

              tp;

             

            DROP Tables t1, SENT, RECEIVED;

            • Re: retrieve the right date
              Massimo Grossi

              SENT :

              Mapping LOAD * inline [

              REPORTING_ID, DATE_APPLI

              2,                      04/05/2015

              ];

               

              RECEIVED:

              LOAD  REPORTING_ID, AS_OF_DATE, DATE_APPLI,

                if(AS_OF_DATE < DATE_APPLI, 9999999, AS_OF_DATE - DATE_APPLI) as DATE_DIFF;         // calc date difference

              LOAD

                REPORTING_ID, AS_OF_DATE,

                ApplyMap('SENT', REPORTING_ID) as DATE_APPLI                                                                     // add DATE_APPLI

              inline [

              REPORTING_ID, AS_OF_DATE

              2,                          03/05/2015

              2,                            05/05/2015

              2,                          10/06/2016 ];

               

              // keep only the min date diff by reporting id

              Right Keep (RECEIVED)

              load

                REPORTING_ID,

                min(DATE_DIFF) as DATE_DIFF

              Resident RECEIVED

              group by REPORTING_ID;