3 Replies Latest reply on Apr 17, 2014 6:36 AM by GiedriusGodelaitis

    2 similar numbers from the list

    ultranix

      Title

      2 similar numbers from the list

      Post

           I have 16 numbers, which are fields in my database (8 for high: h0, h1, h2, h3, h4, h5, h6, h7 and 8 for low: l0, l1, l2, l3, l4, l5, l6, l7) and they appear in every record. These numbers are previous swing highs and previous swing lows. 

           Another important field is price.

           So what I need. In that list of 16 numbers - 1) find the nearest two (or more) similar numbers above the price and 2) find the nearest two (or more) similar numbers below the price

           By saying "similar", I say that they cannot be different by more than 0,5% (.005).

           I count that difference between two numbers with such formula: If (Max (h0; h1) < Min (h0; h1) * 1,005; Max (h0; h1);

           But trying to check all the possible combinations would take 28 If statements and that's not what I want.

           so, let's say, for the above_price field: if the price is 130,21 and h3 = 130,55 and h4 = 130,58, it would return 130,58, which is greater of two similar number that are above the price. 

           and for below_price field: if price is same 130,21 and l2 = 129,12 and h5 = 129,61, it would return 129,61, which is greater of two similar number that are below the price.

           So how do I calculate above_price and below_price numbers from 16 fields I mentioned previously plus the price?

            

            

        • 1. Re: 2 similar numbers from the list
          ultranix

               Is this a rocket science?

          • 2. Re: 2 similar numbers from the list
            philmodjunk

                 Don't know any rocket scientists to ask. wink

                 At the time that you first posted this, I didn't really have the time to sit down and see if there was a better way or not. Any time I see a single record storing a series of fields that store apparently identical data, my first thought is to see if using a table of related records with one record in place of each field might offer options that a set of fields in the same record doesn't have--such as sorting the records, using ExecuteSQL, or using a relationship to match to only certain values of the total set...

                 In your case, I still have questions about what you are trying to do here.

                 Focusing just on the H values for a start, it appears that you want 2 values that are:

                 a) greater than price

                 b) have a difference where the absolute value of H value 1 - H value 2 is less than 1.005 * Min ( H value 1 ; H value 2)

                 c) are closer to Price than any other two H values.

                 Do I have that correctly stated?

                 Obviously the need to evaluate pairs of highs and pairs of lows makes this such a complicated process. When comparing these pairs do you need to evaluate all possible pairings of the high values or only adjacent values in the list. It appears that you need to evaluate all possible pairings and this is what makes this process tedious.

            • 3. Re: 2 similar numbers from the list
              GiedriusGodelaitis

                   Hi Aivaras, I think I can help you, cause we are in the same city and we can talk about this solution in more details. It does not realy clear what is the purpose of this calculation. It may be more easier ways to manage the same problem.

                   Please contact me as I am a FileMaker developer in Vilnius. My email: gg@yzysoft.com

                   Best regards,

                   Giedrius Godelaitis