4 Replies Latest reply on Nov 21, 2016 9:55 AM by Brittney Parker

    Identifying a Date range within a String Dimension

    Brittney Parker

      So the title may not make much sense..Sorry about that. Also, I am unable to attach a sample work book so I'm going to try and give as many details as possible.

       

      I have a field titled 'Order Notes'. These order notes are to be updated by the employees with the goal of every note having been updated with a date and note no greater than a week old. The employees place a date somewhere within the note as 11-18; 11/18; 11/18/16; 11/18/2016; 11,18; 11.18; 11.18.; 11.18.16. There are so many typos and variations it's ridiculous. The person who was previously running this report in Excel was individually looking at each note and deciding if the note was greater than a week old or within the one week range. There are hundreds of order notes to look at and takes some time. My job is to automate this report so this person can focus on other tasks. I decided that the CONTAINS function would probably work best and tried something like this: CONTAINS([Order Notes],DATEADD('week',-1,TODAY())) , but as I imagined the string and date combo wasn't working together and I wasn't 100% sure the DATEADD function was the right choice either.

       

      Here are some mock examples of what I am working with:

      Order Notes

      11/13 We are in need of set up docs, staff reviewed.

      11/17-Staff Reviewed:Order Approved

      11/18/16 Secondary insurance will need to be done and we need the return ticket.

      11,6 staff reviewed and requested set up docs // 10/22 Setup docs needed. Pt.'s approved. Emailed POC.

      11.12 We are in need of set up docs, staff reviewed. emailed POC

      11.13.- Received dated Rx

      11.6.16 Order has been staff reviewed and that we still need set up docs // 10-30 We are in need of set up docs, pt is approved for set up

      Insurance verified On 09/29/2016 // 10/17 We are needing a signed SS. Emailed POC.

       

      What I'm hoping to determine is:

           - If the order notes contain a date greater than a week old from today then place it in a bin titled "Old Note"

           - If the order notes contain a date less than a week old from today then place it in a bin titled "Noted"

           -else  titled "Blank"

       

      Does anyone know of way I can accomplish what I'm wanting? Any help would be much appreciated!!

       

      Regards,

      Brittney

        • 1. Re: Identifying a Date range within a String Dimension
          Norbert Maijoor

          Good morning Brittney,

           

          This is a "scary" one;) If you advise something is not possible within Tableau there will always be a member coming up with a solution;)

          But my "gut feeling" tells me you need "intelligent" text analysis  based on  algorithm's before visualizing it

          Joe Oppelt can you confirm:)

          • 2. Re: Identifying a Date range within a String Dimension
            Sherzodbek Ibragimov

            Brittney,

            I agree with Norbert Maijoor, this can't be very easily achieved by Tableau without a lot of calculations based on current and possible variations of date types in notes. Anyways, I was able to extract date out of notes, but with assumptions that these are only possible variations will be used in future and they are at the beginning of each note (else date will equal to Null). Otherwise, it is really tough to keep up with relevant formulas to extract date based on position of date text within sentence.

             

            Also, I would advise to allow users to input certain or only one date format so that it will be much easier to work with your notes to extract dates sine it is predictable and standardized.

             

            Most importantly, you have to do cost benefit analysis whether we want to go get the most out of Tableau or 100% full dates using 3rd party software that may cost you. You can try: Date Extraction API | AlchemyAPI  or  Text Analysis - Google Sheets add-on for date extraction. Since it is not 100% full date extraction, but at least better than nothing. Again you have to consider cost and benefit of getting 100% full dates.

             

            Please see attached for more info. Thanks

            Sherzod

            • 3. Re: Identifying a Date range within a String Dimension
              Joe Oppelt

              Scary, indeed.

               

              Maybe REGEX can do something here.  It will let you look for patterns.

               

              Creating the bins is easy once you get past the parsing, but it's the parsing that will be tricky.  If I were faced with this task, I would look to see what regex can do for me.

              • 4. Re: Identifying a Date range within a String Dimension
                Brittney Parker

                Thank you all for your input. I will try to get something in place for the users to only use one date format and place it at the beginning of the field.

                 

                Sherzod, thank you for the sample workbook. I think I will be able to use your this for now until we can get a strict format on these notes.

                 

                I will also look into REGEX. Again, thank you guys!!

                 

                Regards,

                Brittney