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

Calculate Number of Days from Large Date Array

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

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

Hi brianb,

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

fmpdude,

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

brianb,

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

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

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

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