2 Replies Latest reply on Jul 11, 2016 8:11 AM by Adam Kastan

    Finding the next highest value in a table

    Adam Kastan

      Hi,

       

      I am looking for a solution for finding the next highest value in a table based on 2 values, contract ID and Mbps number.

       

      The pricing table has Ethernet costs based on ordered Mbps values per contract ID.

      The Site table has a list of site IDs with a Mbps value and Contract ID.

       

      Sometimes the site Mbps values do not match the pricing table Mbps values. I would like to create logic to return the next highest Ethernet cost based on the site Mbps value and contract ID.

       

      For example in the attached workbook site 5AS0823A has a Mbps value of 150 and a contract ID of TELC01. TELC01 in the pricing table has Mpbs values of 10,20,50,100,200 and 400.

       

      The calculation I am looking for would look up the site Mpbs value of 150 and contract ID of TELC01 in the pricing table and return the next highest value which in this case would be 200 ($1946).

       

      Thanks,

      Adam

        • 1. Re: Finding the next highest value in a table
          Aiswarya Sundaram

          Hi Adam,

           

          This should help:

           

          IF ISNULL(ATTR([PRICING_TABLE (DUKENET)].[Ethernet Bw Start])) then

              if SUM([Mbps])<=10 then 10

              ELSEIF SUM([Mbps])>10 and SUM([Mbps])<=20 then 20

              ELSEif SUM([Mbps])>20 and SUM([Mbps])<=50 then 50

              ELSEIF SUM([Mbps])>50 and SUM([Mbps])<=100 then 100

              ELSEIF SUM([Mbps])>100 and SUM([Mbps])<=200 then 200

              ELSEIF SUM([Mbps])>200 AND SUM([Mbps])<=400 THEN 400

          END

          ELSE SUM([Mbps])

          END

           

          Since the MBPS field is also linked in the blending, it resulted in a NULL where it didn't match, I made use of that.

          • 2. Re: Finding the next highest value in a table
            Adam Kastan

            Hi Aiswarya,

             

            Thanks for your reply.  I did not provide a full enough example of my question and your response, while correct, will not work with the fuller dataset I use. I have over 1K+ contracts and 50K+ sites in my dataset. I have updated the workbook with additional contracts and sites to provide a more robust example of my question.

             

            Thanks,

            Adam