Android Question How to implement huge lookup table

[Arf]

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.
Thanks.

 

[Arf]

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.0, 1.3876, -0.0221, -0.0832, _
   3.25, 1.2621, -0.0191, -0.0616, _
   3.5, 1.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.

 

[Arf]

Ok, this compiles but the top line has a warning "lTable is never assigned any value".

Dim lTable( 3.0, 1.3876, -0.0221, -0.0832, _
                3.25, 1.2621, -0.0191, -0.0616, _
                3.5, 1.1460, -0.0167, -0.0420 ) As Double

 

[Arf]

Ok this seems to work fine.

    Dim lTable() As Double = Array(    3.0, 1.3876, -0.0221, -0.0832, _
                                    3.25, 1.2621, -0.0191, -0.0616, _
                                    3.5, 1.1460, -0.0167, -0.0420 )

 

[Peter Simpson]

Why an array and not a list?

 

[Arf]

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).

 

[klaus]

I want to keep it all in one class..., and not use a database.

Why ?

...and not use a database.

Why not ? This would probably be the best solution !

I need to retrieve the spline values using the age.

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.

 

[Peter Simpson]

Here you go @Arf.
Below is an example of a list. I would read your values from a database if possible though.

'Example
    Dim Listy As List
        Listy.Initialize
        Listy.AddAll(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
        Log(Listy.Get(i))
    Next

Run in debug mode...

 

[Arf]

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.

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!

 

[Peter Simpson]

Have you looked at SQLite @Arf?
https://www.b4x.com/android/forum/threads/sql-tutorial.6736/#contenthttps://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content

 

[klaus]

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.

 

[Arf]

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
Thanks

 

[klaus]

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.

 

[Peter Simpson]

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?

my code which extracts stuff out the database (1)

It's a simple SQL query

database libraries (2)

Yes the SQL library in the Library tab

a database in containing coeff's in memory (3)

No, not in memory but in the main database

and I would also need to have a way to populate the database (4)

It's a simple SQL query

BTW @klaus is 100% correct, you need to be using a database...

 

[Devan]

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

 

[Arf]

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 + 4  
    Loop

       'we've found the corresponding row, get the values from the previous row
    age = sTable(tableOffset-4)
    L = sTable(tableOffset-3)
    M = sTable(tableOffset-2)
    S = sTable(tableOffset-1)

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.

 

[JTmartins]

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.

 

[Arf]

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\cgli.java:581: 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?

 

[DonManfred]

And the error

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 )

 

[Arf]

Excellent, that's done it. Thanks