1 2 3 4 5 6 7 Previous Next 98 Replies Latest reply on Jan 12, 2015 7:34 AM by danny.lackey Go to original post
      • 45. Re: Need to create buffer around points.
        danny.lackey

        Another weird thing that is happening is with the "Conflict Details".  It's comparing all to the 300ft radius, which in this work book, I don't believe there are any jobs for that crew yet.  Should all be 150 for crew 1 and 2.

        • 46. Re: Need to create buffer around points.
          Richard Leeke

          Your Self_Team approach will result in pretty much the same result. Doing it in the datasource would be better because it will reduce the size of the extract. If you only ever want to look at conflicts between teams then there is no point in including rows for all permutations of wells foe the same team in your extract.

           

          Similarly, I think it should be possible to express the date overlap condition as a datasource filter, which will mean that the extract only includes rows for pairs of "jobs" (not quite sure what you call an instance of a team using a well) which overlap in time.

           

          Here's the condition you need (you need both lines to account for overlap at either end of the period).

           

          ([TEAM].[Start_Date] >= [CM_DATA1_TEAM].[Start_Date] AND [TEAM].[Start_Date] <= [CM_DATA1_TEAM].[End_Date]) OR

          ([TEAM].[End_Date] >= [CM_DATA1_TEAM].[Start_Date] AND [TEAM].[End_Date] <= [CM_DATA1_TEAM].[End_Date])

           

          I'll have to have a think about how best to express that in a way that gets evaluated before the extract is created. It would definitely work if you embedded it in a custom SQL connection, but it may be possible to define a calculated field which you use to define a datasource filter. It's a long time since I played in that space so I'm going to need to try it.

           

          Let me know how you go. If you get stuck I can try to mock something up with Sample Coffee Chain data - or you can post where you have got to.

          • 47. Re: Need to create buffer around points.
            danny.lackey

            Man I was close with what I was trying.  Your suggestion made it work for all cases.

             

            EDIT - Almost all cases.  It's missing the last one in the highlighted set I posted.  I thought maybe it had an issue where the end date of one was = to the start date of the next, but the code accounts for that.  It did catch others where this was the case.  Not sure why it's missing that one....  Still playing with it.

             

            Here's my code:

             

            ([START_DTE] >= [CM_DATA1_START_DTE] AND [START_DTE] <= [CM_DATA1_END_DTE]) OR

            ([END_DTE] >= [CM_DATA1_START_DTE] AND [END_DTE] <= [CM_DATA1_END_DTE])

            • 48. Re: Need to create buffer around points.
              Richard Leeke

              I seem to recall that your start date field is actually a date time. Maybe they don't all have time set to midnight?

               

              Try formatting those as date time in the display and that will make it obvious. If that't the issue you can either fix the underlying data or truncate all the dates in the expression with DATETRUNC('day', [START_DTE]).

              • 49. Re: Need to create buffer around points.
                danny.lackey

                Great suggestion.  Looks like all of these show 12:00, so that shouldn't be a factor.

                • 50. Re: Need to create buffer around points.
                  Richard Leeke

                  Careful - there's a mixture of 12:00 am and 12:00 pm in there. I noticed that in the sample data you sent last week, too. At a quick glance I couldn't see your specific example you are having trouble with, but there are quite a mixture.

                   

                  Datetimes.PNG

                  • 51. Re: Need to create buffer around points.
                    danny.lackey

                    Interesting!  It's especially weird because I have the dates all formatted to short date in the source (Access) dB.  I double checked the table and there aren't any times showing there.  Guessing it must be underlying somehow.  I'll look into it.

                    • 52. Re: Need to create buffer around points.
                      danny.lackey

                      As for the conflict details evaluating everything to 300 for the field display.  Each of the offending wells have a 150ft buffer, so adding them together would produce the 300.  Are we intentionally adding their buffers together?  I can see how that would make sense.

                       

                      IF (ATTR([Separation]) < ATTR([BUFFER] + [CM_DATA1_BUFFER])) AND (ATTR([Id]) < ATTR([CM_DATA1_Id])) THEN

                          LOOKUP("Separation: " + STR(ROUND(ATTR([Separation]))) + " is less than required: " + STR(ATTR([BUFFER] + [CM_DATA1_BUFFER])), 0)

                      ELSE

                          ""

                      END

                      IF (ATTR([Separation]) < ATTR([BUFFER] + [CM_DATA1_BUFFER])) AND (ATTR([Id]) < ATTR([CM_DATA1_Id])) THEN
                          LOOKUP("Separation: " + STR(ROUND(ATTR([Separation]))) + " is less than required: " + STR(ATTR([BUFFER] + [CM_DATA1_BUFFER])), 0)
                      ELSE
                          ""
                      END

                      • 53. Re: Need to create buffer around points.
                        Richard Leeke

                        If each well has a 150 foot buffer circle, the wells need to be 300 feet apart to avoid the circles intersecting. So yes, it's deliberate.

                         

                        On the dates, formatting won't affect the underlying storage - you need to look at how the date is captured or just truncate the date in the condition as per my earlier comment.

                         

                         

                        Sent from my phone - excuse the weird typos.

                        • 54. Re: Need to create buffer around points.
                          danny.lackey

                          Going smooth.  I'm coming to the conclusion that I need to figure out how to create these circle calculations in Access, because the wait time for each interaction is a little too long - even with an extract.  Do you agree that that would speed things up?

                          • 55. Re: Need to create buffer around points.
                            Richard Leeke

                            Not necessarily. Extracts will have the disadvantage of needing to refresh when the D/B changes, but should be much quicker after that - especially if you followed my suggestions about putting the conditions about conflicts between teams as part of the join condition and the date overlap as a data source filter to minimise the size of the extracts (and also the extract creation time). Also - did you take my re-worked version of the calculations to push them back to the data engine (i.e.getting rid of all the ATTR() and LOOKUP() tricks I had used to make them happen in Tableau to avoid the Access restriction)? If not, I'd try those things first.

                            • 56. Re: Need to create buffer around points.
                              danny.lackey

                              I have not implemented the date overlap as a data source filter because the customer wants to show all work and just flag the conflicts.  I believe filtering to just those conflicts would no longer show the non-conflicting jobs.  I'll have another look at the re-worked techniques.  Thanks!

                              • 57. Re: Need to create buffer around points.
                                Richard Leeke

                                All jobs would still show in the circles view, just not in the conflicts view. But I can see that you may not want to put that in the datasource.

                                • 58. Re: Need to create buffer around points.
                                  danny.lackey

                                  I'm not seeing any ATTR() or LOOKUP() references in the reworked version.  I might not be following what you are suggesting.

                                  • 59. Re: Need to create buffer around points.
                                    Richard Leeke

                                    I thought you had based your version on my earlier one with those tricks on it. I removed them from the most recent one I posted, do I was just suggesting you make sure you are using the latest versions of the calculations.