5 Replies Latest reply: May 15, 2014 10:08 AM by Bill Watson RSS

    Floating Trap for Dates

    Walter K.

      I'm have trouble using a floating trap when dates are in different formats, i.e.,

      on the same report there may be a date in one of the following formats: m/d/yyyy, m/dd/yyyy or mm/d/yyyy.

       

      Is there a way to successfully trap these dates consistently using a floating trap or I'm out of luck because the floating trap needs sample and data to always be in the exact same format?

       

      Thanks

      Walter

        • Floating Trap for Dates
          Data Kruncher

          Hello and welcome to the Monarch forum Walter.

           

          Of course, exactly how one builds a floating trap can vary based on the sample text. With floating traps, it's particularly critical and challenging.

           

          Without having your particular challenge at hand, I build a date-specific demo.

           

          Using this text as the report (admittedly this is a pretty limited test for floating traps, but I expect the approach to work with larger samples) to test varying widths:

          1/1/2010  

          12/1/2010 

          1/12/2010 

          12/12/2020

          /codeI began to build the template. When intending to use floating traps I find it best to choose the widest data as the sample text, so that became the 12/12/2010 line.

           

          I then put the two / characters in the proper position and checked the Floating Trap option. Now here's the (inevitable) twist, and you probably encountered this too.

           

          Monarch won't let you paint the entire date field.

           

          So to overcome this, paint three different Character (not Numeric) fields for the Month, Day and Year. Be sure to not include the / characters.

           

          Save the template and move on to the Table window. Create a new formula-based Date type calculated field using this expression:

          CtoD(Month"/"Day"/"Year,"m/d/y")[/code]And that's it. Floating trap dates. Optionally, hide the date components Month, Day and Year from view.

           

          Look for a full discussion of floating traps in an upcoming post on [URL="http://********************"]********************[/URL].

           

          HTH,

          Kruncher

            • Floating Trap for Dates
              Olly Bond

              Hello Walter,

               

              The same principle, but using one field fewer, works if you trap the /YYYY portion of the date using the N numeric trap, as you can then select the MM/DD as one field.

               

              Being able to select the characters that match the floating trap expression as part of a field might be a nice future feature for Monarch?

               

              Best wishes,

               

              Olly

                • Floating Trap for Dates
                  Bill Watson

                  in any report where date format is variable you are going to run into trouble.

                   

                  for example if i write 12/1/2010

                   

                  do i mean 12th January 2010 or 1st December 2010. It all depends on your point of reference. Most US folks would opt for the latter interpretation, whereas most UK (and a lot of europeans) would opt for the first interpretation.

                   

                  even worse is 10/1/12 -

                   

                  is that 10th January 2012

                  is that 1st October 2012

                  is that 12th January 2010

                  is that 1st December 2010

                   

                  In your report is there some other field in each record by which you can establish the date type used. For example a report detailing invoices from different regions may have the "US Office" records in MM/DD/YYYY, the "UK Office" has DD/MM/YYYY, the "Far East Office" has YYYY/MM/DD

                   

                  If this is the case you could simply trap the date as a character field and then use Lsplit to break up and reformat the elements based on the location.

                   

                  I would suggest however you go back to the source of the report and ask if they can standardise on one format. This is usually the best option rather than working around the issue - because at some point in the future another non standard date will appear and you will have to re do your work to get around it.

                    • Floating Trap for Dates
                      elginreigner _

                      Bill, I dont think his issue is with the format of the date itself but rather the values of month and day growing in size depending on the month or day, i.e. October (month 10) versus January (month 1). Where as the digit size of month changes.

                       

                      Can we see some sample data? There may be a better trap for the growing date.

                        • Floating Trap for Dates
                          Bill Watson

                          on the same report there may be a date in one of the following formats: m/d/yyyy, m/dd/yyyy or mm/d/yyyy.[/QUOTE]

                           

                          Bill, I dont think his issue is with the format of the date itself /QUOTE

                           

                          maybe in my old age my grasp of english is failing me.. but it seems he has issues with multiple date formats also, so I think my point stands re telling m/d & d/m or mm/dd & dd/mm formats apart for picking up the correct details.