Android Question How to implement huge lookup table

Discussion in 'Android Questions' started by Arf, May 28, 2015.

  1. Arf

    Arf Active Member Licensed User

    I've got some C# code I need to get into my B4A project, and it has a big lookup table.
    In the C# original code, the DataSet class is used. The table data consists of around 400 rows with 4 columns containing doubles and, ints.

    I also have the lookup table data in excel, so I thought it would be easiest if I can somehow format the data in excel and paste it in as some form of const definition, but I'm having a bit of a brain fail as to where to begin, and I'm not certain what the most memory efficient way of doing this is.

    Here's a paste of the first few lines of the 400 in excel
    Age Lspline Mspline Sspline
    3 1.3876 -0.0221 -0.0832
    3.25 1.2621 -0.0191 -0.0616
    3.5 1.1460 -0.0167 -0.0420
    3.75 1.0382 -0.0147 -0.0242
    4 0.9377 -0.0132 -0.0085
    4.25 0.8437 -0.0122 0.0047

    Has anyone got any suggestions for a starting point? I need to retrieve the spline values using the age.
  2. Arf

    Arf Active Member Licensed User

    I want to keep it all in one class, and not use a database. If using C I would stick it in a header file as an array.
    I tried a multidimensal array but I see they can't be defined easily,so I thought I'd deine a single array and I can just increment through the table 4 elements at a time to loook at the numbers in the first column to find matches.

    Dim lTable() As Double =( _
    3.01.3876, -0.0221, -0.0832, _
    3.251.2621, -0.0191, -0.0616, _
    3.51.1460, -0.0167, -0.0420 )

    Trouble is it won't compile, error is ") expected" on the first line, I am not sure why, I've got that _ thing in there and after a space too.
  3. Arf

    Arf Active Member Licensed User

    Ok, this compiles but the top line has a warning "lTable is never assigned any value".
    Dim lTable( 3.01.3876, -0.0221, -0.0832, _
    3.251.2621, -0.0191, -0.0616, _
    3.51.1460, -0.0167, -0.0420 ) As Double
  4. Arf

    Arf Active Member Licensed User

    Ok this seems to work fine.
    Dim lTable() As Double = Array(    3.01.3876, -0.0221, -0.0832, _
    3.251.2621, -0.0191, -0.0616, _
    3.51.1460, -0.0167, -0.0420 )
    Peter Simpson likes this.
  5. Peter Simpson

    Peter Simpson Expert Licensed User

    Why an array and not a list?
    Devan likes this.
  6. Arf

    Arf Active Member Licensed User

    I wasn't sure how I could populate a list in a simlar manner, so tried to keep it as simple as possible (lists are quite new to me).
  7. klaus

    klaus Expert Licensed User

    Why ?
    Why not ? This would probably be the best solution !
    What exactly do you mean with this ?
    What do you need to retreave ?
    This is the main point how to define the next steps, and probably suggest a database !
    But without knowing more in detail what you want to do it's difficult to give a concrete advice.

    For the display the Table Class might be a solution, there you can easily import SQLite databases or *.csv files.
    RandomCoder and Peter Simpson like this.
  8. Peter Simpson

    Peter Simpson Expert Licensed User

    Here you go @Arf.
    Below is an example of a list. I would read your values from a database if possible though.
        Dim Listy As List
    Regex.Split(", ""3.0, 1.3876, -0.0221, -0.0832, 3.25, 1.2621, -0.0191, -0.0616, 3.5, 1.1460, -0.0167, -0.0420"))

    For i = 0 To Listy.Size - 1
    Run in debug mode...
    Last edited: May 28, 2015
    RandomCoder and Devan like this.
  9. Arf

    Arf Active Member Licensed User

    Well, the table (and there are 10 tables of 400 x 4) contains data that will never change, they're coefficients for equations to calculate some parameters about the human body. The first value is an age argument, then follow three coeffients. Then the next row starts with age again, etc. The youngest age is 3 and it goes up to 95, in jumps of a 1/4 of a year.

    So say my patient is 22.3 years old. I need to scan the array to find the AGE that is just below 22.3 (which will be 22.25) and retrieve the next 3 coefficients, and then retrieve the next 3 (which will be for age 22.5).
    Then I need to perform an interpolation between each of a 3 pairs of values to get a final 3 coefficents, which I can use to work out my answer.

    Since this data never, ever changes I prefer to get it all in one file where it will live forever, job done. No need for creating databases, using files etc.
    I am not a very good programmer so I usually opt for the simplest solution, so I can understand what I'm doing!
  10. Peter Simpson

    Peter Simpson Expert Licensed User

  11. klaus

    klaus Expert Licensed User

    The only solution is a database !
    You can filter on ages and get the coefficients easily !
    Databases may seem difficult at the beginning, I had the same feeling before I learned to use them.
    But at the end they are by far not that complicated but very powerful.
    You find a whole chapter in the User's Guide with examples including the source code.
    RandomCoder and Devan like this.
  12. Arf

    Arf Active Member Licensed User

    Thanks guys, I have used sqlite to store the patients and their details in, but for this it seems silly.
    So I would then have: my code which extracts stuff out the database (1), database libraries (2), a database in containing coeff's in memory (3), and I would also need to have a way to populate the database (4).
    If I can make a C style lookup table directly coded in the class, then would only have: my code (1)

    Which seems a lot simpler and quicker and more efficient.

    I'll come back and seek further advice if my simplistic approach fails :)
  13. klaus

    klaus Expert Licensed User

    Sorry, but your explanation is not clear to me.
    The answeres you got were about your question in the first post !
    If your problem is different, then you need to explain it more in details.
    I am still convinced that databases are the solution.
    RandomCoder, Devan and Peter Simpson like this.
  14. Peter Simpson

    Peter Simpson Expert Licensed User

    You have gotten yourself all confused, 1, 2, 3 and 4 all uses the SQL library and is basically the same thing. It's not difficult at all. Wait a minute, this could actually be my fault and I should have asked you this question before. Have you ever used a database before @Arf?

    It's a simple SQL query

    Yes the SQL library in the Library tab

    No, not in memory but in the main database

    It's a simple SQL query

    BTW @klaus is 100% correct, you need to be using a database...
    RandomCoder and Devan like this.
  15. Devan

    Devan Member Licensed User

    Very Good Explanation Klaus & Peter Simpsons. I find SQL much more easier. ;)
    @Arf you should look into their advice.
    Thank you Guys...
    RandomCoder likes this.
  16. Arf

    Arf Active Member Licensed User

    I have used databases before, but simplistic ones. How would I get my tables of coefficients into a database in the first place easily?

    I've done the table lookup now, not tested yet but here's what it looks like:
    'the table (just first few lines, there's over 300 lines)
    Dim sTable() As Double = Array(    3    ,    1.3876    ,    -0.0221    ,    -0.0832 _
    3.25    ,    1.2621    ,    -0.0191    ,    -0.0616 _
    3.5    ,    1.1460    ,    -0.0167    ,    -0.0420 _
    3.75    ,    1.0382    ,    -0.0147    ,    -0.0242 _
    4    ,    0.9377    ,    -0.0132    ,    -0.0085 _
    4.25    ,    0.8437    ,    -0.0122    ,    0.0047 _)
    Then the I use patient age as an argument to scan down the values in the first 'column' of the table to find the row which is just above patient age
    'do lookups
        Do While sTable(tableOffset) < patAge
            tableOffset = tableOffset + 

    'we've found the corresponding row, get the values from the previous row
        age = sTable(tableOffset-4)
        L = sTable(tableOffset-
        M = sTable(tableOffset-
        S = sTable(tableOffset-
    And job done.. I use excel and textpad macros to format the tables of data in excel so I can just cut and paste them into my class, it only takes 20 seconds per table.
  17. JTmartins

    JTmartins Active Member Licensed User

    You can export from Excel to CSV, than populate the database from the CSV file.

    quite easy...take a look here

    after that a few queries will give you whatever result you are looking for.
    Peter Simpson likes this.
  18. Arf

    Arf Active Member Licensed User

    I've got a compilation error with my arrays, not sure why.
    My array definitio (shortened version):
    Dim table() As Double = Array(    3    ,    0    ,    -0.2311    ,    0.3351, _
    3.25    ,    0    ,    -0.2170    ,    0.3098, _
    3.5    ,    0    ,    -0.2040    ,    0.2862, _
    3.75    ,    0    ,    -0.1922    ,    0.2643 )
    And the error:
    Dim table() As Double = Array( 3 , 0 , -0.2311 ,
    javac 1.7.0_51
    src\SpiroConnect\Home\ error: inconvertible types
    _table = (double[])(new Object[]{(Object)(3),(Object)(0),(Object)(-0.2311),(Object)(0.3351),(Object)(3.25),(Object)(0),(Object)(-0.2170),(Object)(0.3098),(Object)(3.5),(Object)(0),(Object)(-0.2040),(Object)(0.2862),(Object)(3.75),(Object)(0),(Object)(-0.1922),(Object)(0.2643)});Debug.locals.put("table", _table);Debug.locals.put("table", _table);
    required: double[]
    found: Object[]

    Have I got my syntax wrong somewhere?
  19. DonManfred

    DonManfred Expert Licensed User

    have you tried to write it like this

    Dim table() As Double = Array As Double (    3    ,    0    ,    -0.2311    ,    0.3351, _
    3.25    ,    0    ,    -0.2170    ,    0.3098, _
    3.5    ,    0    ,    -0.2040    ,    0.2862, _
    3.75    ,    0    ,    -0.1922    ,    0.2643 )
    Peter Simpson and Arf like this.
  20. Arf

    Arf Active Member Licensed User

    Excellent, that's done it. Thanks :)