5 Replies Latest reply: Sep 20, 2012 6:30 AM by Stefan Wühl RSS

    showing rows that are 'out of date'

      Hi everybody,

       

      I have the following problem:

       

      The table is as following:

       

       

      Client              Package#         Status          Delivery Date

      1                           1               Sent               01-05-2012

      1                           1               Delivered         02-05-2012

      1                           1               Sent               10-05-2012

      1                           4               Sent               10-05-2012

      2                           5               Sent               20-06-2012

      2                           5               Delivered         30-06-2012

      3                           1               Sent               05-08-2012

      3                           1               Sent               15-09-2012

      3                           1               Delivered         17-09-2012

       

       

      Here i want to show only the Rows where a 'Package#' of a 'Client' has been 'Sent' but not been 'Delivered' within 7 days or not been delivered at all.

      The package number can be reused in the future.

       

       

      The result must show the following:

       

      Client              Package#         Status          Delivery Date

      1                           1               Sent               10-05-2012

      1                           4               Sent               10-05-2012

      2                           5               Sent               20-06-2012

      3                           1               Sent               05-08-2012

       

      I was told that this could be achieved within the script with a mapping load, but i would not know how.

       

      Any help is appreciated.

       

      Thanks,

      SY

        • Re: showing rows that are 'out of date'
          Sebastian Blum

          Hi,

           

          First of all, you should avoid to re-use the package#. Would this be possible? Furthermore, does a date in the column Delivery Date mean sent date if status is Sent, or is this expected delivery date if status is Sent? This will not be easy to solve if you don't change methodology in order to generate unique keys.

           

          Brgds,

          sebablum

          • Re: showing rows that are 'out of date'
            Jose Tos

            Client              Package#         Status          Delivery Date    Delivered Date

            1                           1               Sent               01-05-2012     02-05-2012

            1                           1               Delivered         02-05-2012     02-05-2012

            1                           1               Sent               10-05-2012     02-05-2012

            1                           4               Sent               10-05-2012     -

            2                           5               Sent               20-06-2012     30-06-2012

            2                           5               Delivered         30-06-2012     30-06-2012

            3                           1               Sent               05-08-2012     17-09-2012

            3                           1               Sent               15-09-2012     17-09-2012

            3                           1               Delivered         17-09-2012     17-09-2012                                                                                

             

            I would create this structure and grouping by Client, Package#, if Delivered Date is greater than Delivery Date, i would create a Flag in another column to know wich has been delivered and wich one doesn´t, all this in the load script

             

            if you have problems to make the code, tell me.

            The first structure is with a left join by Client, Package# and you can make the calculations to create the Flag with a resident load of this previous table.

             

            Regards

            • Re: showing rows that are 'out of date'
              Stefan Wühl

              How do you distinguish between the two packages sent for client 3?

               

              3                           1               Sent               05-08-2012

              3                           1               Sent               15-09-2012

              3                           1               Delivered         17-09-2012

               

              You expect following outcome:

               

              3                           1               Sent               05-08-2012

               

              So I assume that the package sent on 15-09-2012 has been delivered. But how do you know?

              What if the package sent on 05-08 has been delivered? That means you would expect to see both lines

               

              3                           1               Sent               05-08-2012

              3                           1               Sent               15-09-2012

               

              right? First the one that has been delivered, but not within the allowed period of days, second the one that has not been delivered yet.

               

              IMHO, your problem seems to not provide full information to retrieve an unambiguous answer. Do you have any more information in your table that could help us here?

                • Re: showing rows that are 'out of date'

                  Hi,

                   

                   

                  @sebablum : the delivery date is the date of the status. When a package has been 'Sent' it gets a date. Same for the status 'Delivered' when the package is delivered it gets a date.

                   

                  @José Mª Tos : I will let you know if this has worked for me.

                   

                  @swuehl : There are more information in the table but none that will make the row unique.

                   

                  The answer i got from the client when i explained this was : the date that is nearest to the delivery date of the 'Package' that is 'delivered' is the package that has been 'sent'.

                   

                  So if you got two of the same packages number with the status sent date on 20-02-2012 and the other 21-02-2012 and the delivery date of status delivered = 22-02-2012, then the package with the status 'Sent' on 21-02-2012 is the one that has been delivered.

                    • Re: showing rows that are 'out of date'
                      Stefan Wühl

                      Then you can try maybe like this:

                       

                      SET DateFormat='DD-MM-YYYY';

                       

                      INPUT:

                      LOAD *, Client&'-'&[Package#] as ClientPack INLINE [

                      Client,              Package#,         Status,          Delivery Date

                      1,                           1 ,              Sent      ,         01-05-2012

                      1,                           1 ,              Delivered ,        02-05-2012

                      1,                           1 ,              Sent      ,         10-05-2012

                      1,                           4 ,              Sent      ,         10-05-2012

                      2,                           5 ,              Sent      ,         20-06-2012

                      2,                           5 ,              Delivered ,        30-06-2012

                      3,                           1 ,              Sent      ,         05-08-2012

                      3,                           1 ,              Sent      ,         15-09-2012

                      3,                           1 ,              Delivered  ,       17-09-2012

                      ];

                       

                      Result:

                      LOAD *,

                      if(not DeliveredFlag or DeliveryDuration >7, 1) as  NotDeliveredOrLate;

                      LOAD *,

                      if (Status = 'Sent',if(peek(Status)='Delivered' and peek(ClientPack)= ClientPack,1,0)) as DeliveredFlag,

                      if(Status = 'Sent' and peek(Status)='Delivered' and peek(ClientPack)= ClientPack, peek([Delivery Date])-[Delivery Date] ) as DeliveryDuration

                      resident INPUT order by Client, [Package#], [Delivery Date] desc;

                       

                      drop table INPUT;

                       

                      Hope this helps,

                      Stefan