8 Replies Latest reply on Jul 28, 2017 5:24 PM by fmpuser0

    Calculate Number of Days from Large Date Array

    fmpuser0

      I have a DB of flights with dates, origin cities and destination cities. It can be found at https://goo.gl/oRcZad

       

      Each city has a four-character identifier. The first or the first two characters determine a certain geographical area in the world.

       

      I need to calculate the number of days spent at each geographical location K, LL, LW and any others. For the calculation, I must include the time that elapses from one flight to the next. For example,

       

      01/07 KAAA KBBB

      01/13 KBBB KEEE

      01/13 KEEE KBBB

      01/14 KBBB LWZZ

      01/19 LWZZ LLAA

      01/22 LLAA LWZZ

       

      The number of days in K should be 7, i.e. from 01/07 to 01/14.

      The number of days in LW should be 5, i.e. from 01/14 to 01/19.

      The number of days in LL should be 3, i.e. from 01/19 to 01/22.

       

      Is this possible?

       

      Thanks for any help.

        • 1. Re: Calculate Number of Days from Large Date Array

          Are the location codes in the same position within the texy strings?  Or do you have a table where the location codes are identified  so that a relationship can be made?   sorry was on mobile when i replied.

           

          if you have a table with location code you can create a relationship  between the flight table and location code table

           

           

          flight table may need an additional field to isolate the loc code since the code can be either 1 or 2 characters in the text string

           

          FlightTable::LocCode =  LocTable::LocCode

           

          ex.

          Flight Code                   Date              Loc Code        DepartDate

          01/07 KAAA KBBB          1/7/17                K                 =Date + (LocCode::Days)

          01/13 KBBB KEEE         1/13/17              KB

          01/13 KEEE KBBB            etc...                K

           

           

          LocTable

           

          LocCode         days       

          K                  7

          KB                3

          KW                 1

          • 2. Re: Calculate Number of Days from Large Date Array
            fmpdude

            When you're coding, anything is possible.

             

            However, as currently described, you have too much behind the scenes "magic" logic that determines date ranges. I would seriously recommend a more normalized database approach with clearly defined fields.

             

            So, for example, if "K" means 7 days, then have "K" in a separately linked table with "7" as another clearly named field. In that way, the logic is much clearer.

             

            Having lots of implicit logic for complex field names as in your example above is a recipe for lots of problems (bugs). Plus, if any database person has to maintain your software, they will probably have problems understanding it, too.

             

            So, as with many questions asked here, the issue about proper database design will solve most of your problems. Check out the DB design course on Lynda.com and invest a couple hours. You'll probably be glad you did.

            • 3. Re: Calculate Number of Days from Large Date Array
              fmpuser0

              Hi brianb,

               

              Thank you very much for your comments.

               

              Yes, I do have a table with location codes that I have added to the DB (same URL).

              I am a neophyte regular user of FMP with no coding experience so I am not sure how to implement what you suggest. Would it be possible for you to give me detailed instructions or perhaps work on the file itself?

              • 4. Re: Calculate Number of Days from Large Date Array
                fmpuser0

                fmpdude,

                 

                Thanks for your response.

                 

                K does not mean any number of days. It refers to a geographical location.

                 

                I have no control over the structure of the DB. I am forced to work with what I have.

                • 5. Re: Calculate Number of Days from Large Date Array

                  Unfortunately, I prefer not to touch people's files.  By Trade I am not a computer guy and/or a FMP guy and would hate to mess something up.

                   

                  As for setting giving instructions.  if you can provide example tables and related fields and relationships with a few entries of data each to show a representation  of what you are trying to achieve. I could help walk you through it, or even better someone may have a better solution.

                  • 6. Re: Calculate Number of Days from Large Date Array
                    fmpuser0

                    brianb,

                     

                    You can see everything you are asking for in the file that can be downloaded from https://goo.gl/oRcZad

                     

                    This file contains sample data that you are free to manipulate.

                    • 7. Re: Calculate Number of Days from Large Date Array
                      user19752

                      For K

                      Case ( Left ( Origin City ; 1 ) = "K" ;

                      Date - GetNthRecord ( Date ; Get(RecordNumber) - 1 )

                      )

                      This calulate days on each record. (On first record it can't be calculated, then return '?'.)

                      You can add them making summary field for K.

                       

                      Your data have some error that

                      first 4 records don't match Origin City and Destination City in previous record

                      Flt# 14779 records have older Date than previous record

                      1 of 1 people found this helpful
                      • 8. Re: Calculate Number of Days from Large Date Array
                        fmpuser0

                        Your solution worked like a charm. Much appreciated. I have sent you a private message.