2 Replies Latest reply on Nov 11, 2019 10:09 AM by DanaClaire Discher

    Find and return value for a previous string entry

    DanaClaire Discher

      Hello,

       

      I have a dataset where a single order will be submitted for inspection multiple times. The order number is alphanumeric string, and each submission for inspection has a sequence number. I created a calculated field to give me [Unique Order Inspection ID] = ([Order]+[Inspection Sequence #]). The date an order is submitted for inspection is different from the date the order is actually inspected (there's a queuing process).

       

      I need to know the number of days from an inspection to the next time it is submitted for inspection.

       

      I created a calculated field for the [Previous Unique Order Inspection ID] = ([Order]+STR([Inspection Sequence #]-1)).

       

      Now how can I get the Previous Unique Order Inspection's [Inspected Date], so that I can calculate the difference from the previous [Inspected Date] to the [Submitted Date]?

       

      For example, [Unique Order Inspection ID] 12-345678A2 [Submitted Date] 2/3/2019 - [Previous Unique Order Inspection ID] 12-345678A1 [Inspected Date] 1/5/2019 = 29 [Days from Previous Inspection to Submission]

       

      I tried using the CASE formula, but it doesn't seem to be the appropriate formula. I feel like a LookUp of some kind should work to Find the [Unique Order Inspection ID] that matches the  [Previous Unique Order Inspection ID] and Return the [Inspected Date].  I'm hoping to do this as a calculated field (rather than a window calculation) so that I can use the value in this calculated field in other calculations.

       

      By knowing the number of days between inspection and subsequent submission, I can analyze across thousands of orders (not in this data sample) if there's a trend by the [Submitted By] field, if certain people resubmit their orders for inspection faster than others. I can also filter out the [Days from Previous Inspection to Submission] of any submissions after the [First Passed Date Per Order]. Perhaps the goal is to have all orders pass an inspection within 60 days, I could see who in the Submitted By field is meeting that goal across their orders. If someone (Bob) isn't meeting that goal, it could be because of the long times between submissions prior to the first passed inspection. But I may not care that Jane has long times between submissions after the first inspection has been passed, and so I will filter out those [Days from Previous Inspection to Submission] values if the Inspected Date is after the [First Passed Date Per Order].

       

      Please let me know what the right formula would be to return a dimension (in this case [Inspected Date]) from a specified string entry (in this case [Previous Unique Order Inspection ID]).

       

      Thank you!

        • 1. Re: Find and return value for a previous string entry
          Zhouyi Zhang

          Hi, DanaClaire

           

          You are in the right direction. Please find my solution attached.

           

          Hope this helps

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: Find and return value for a previous string entry
            DanaClaire Discher

            Thank you, Zhouyi. That helps to calculate the date difference in a table, but I'm finding that I can't use the table calc results to do additional calculations with. For example, I can't average the [Days from Previous Inspection] by the [Submitted By] field.

             

            Is there a way to look up the [Previous Inspection Date] and just return that (without a table calc)? Then for each row, I could have the calculated field of the date difference from the [Previous Inspection Date] to the [Submitted For Inspection Date], and then do additional calculations, such as the MAX days between Inspection and subsequent submission by [Order] or by [Submitted By].

             

            I was also hoping that the formula to look up a previous value could be used to look up other dimension values. For example, I'd like to be able to look up the Previous Submitted By. So for [Unique Order Inspection ID] 12-345678A5 it would look up the [Previous Unique Order Inspection ID] 12-345678A4 and Return [Submitted By] "Jane".

             

            Appreciate your creativity and help!