We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Need help in calculating field basis grid

Comments

5 comments

  • Avatar
    Adrian Williams

    There is not really enough information in your post on what you are trying to achieve. What do you mean by a 'dynamic grid'?

    Please provide a small sample (one or two records) for each of the two data sets and an example of the expected output data.

    0
    Comment actions Permalink
  • Avatar
    Prashant

    File 1
    Amount
    1000
    501
    24000
    23000

    File 2
    From To. %
    0. 1000. 1
    1001. 10000 2
    10001. 25000 3
    25001 more 4

    But the grid of file 2 can change any time and output should be as per grid.....

    The code should not be hard-coded and should calculate basis the grid

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    I'm not sure I still understand what you ar trying to achieve. What are the logic/ rules that dictate how the data will be processed?

    0
    Comment actions Permalink
  • Avatar
    Prashant

    There are 3 sheets in attached file. 

    Grid

    Data

    Output

    We need to calculate fees on amount basis the grid provided. Refer the output sheet for desired outcome.

    The only submission here is the grid may change for Fees or additional brackets ... and code should not be hard coded. It should do calculation basis the grid.

     

     

    Attached files

    1.xlsx

     

    0
    Comment actions Permalink
  • Avatar
    Gerry Mullin

    I made an attempt at solving this using Json and the eval() function. The first step is to create Json. The Json key contains a single line Python if statement that will evaluate to true or false if the variable "val" is within the range. In the event of being it true, the fee amount to be charged is contained in the Json value.

    After the Json has been constructed, we then bring it in as a field in the main dataset and then proceed to execute each of the Json keys (as they are actually Python statements) using eval(). When we get a true result, we can extract the fee amount from the Json value. The code is relatively simple, you just need some understanding of Json and eval().

    You can read more about eval() here.

     

    Attached files

    Fee Range - 18 Aug 2021.lna

     

    0
    Comment actions Permalink

Please sign in to leave a comment.