Lookup Table

[GabrielM]

Trying to learn some B4A and would like to use a math formula that involves looking up in a hard coded table (two colums) of some sort.

Similar to what in excel is:

LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

Do we have something similar in B4A , or some minimal guidance on how to approach this function, please ?

 

[Merlot2309]

Hello,

Did you have a look at the various samples on the Home page?
(SQL for example)

A "Select" statement functions like a Lookup function in Excel.

Helen

 

[stevel05]

Have a look at Map (Collections (Core)) in the help viewer, or Multi Dimensional Arrays (Core Keywords) in the help viewer.

Map would probably suit you better.

Steve

 

[agraham]

Look at Map in the help. It might do what you want.

 

[GabrielM]

There are basically two columns on 500 rows holding numeric values. Based on the input value to be looked up, I have to find the specific row holding that value on the first column then read the value on the same row in the second column.

Map looks interesting and I will start on it.

Thank you

 

[GabrielM]

Looking fine so far.

Initially I was trying to "produce" a Map file myself and place it in the path that will be passed for reading the Map from file. All OK except the order of the Keys that were imported from the file are not same like in the file.

I think I might got it wrong that the keys/values pairs listed on each line in a file can be addressed say by line number(or index if you like) after importing them with File.ReadMap .

On the other hand if I only use Map.Put(key,val) to add elements in my declared Map I can though address them by their index as in the "LookupTable" sub below, and I get the results as expected.

Anyways, if I comment the line:

Map1 = File.ReadMap(fPath,filename)

I can use the "LookupTable" sub below for searching in the declared Map which is initialized at first Activity_Create then populated with the 500 about key/value pairs at run time (which takes a few seconds though).

Sub LookupTable(parameter_value As Double)              ' the value that we will be searching by in column 1
   Dim fPath As String       : fPath=File.DirDefaultExternal
   Dim filename As String      : filename="searchtables.ini"
   Dim lookup_pre, lookup_post As Double
   'Dim lookup_vector, result_vector As Double           ' declared in Sub Process_Globals
   'Map1.Initialize executed at Activity_Create(Firstime as Boolean) 
   '
   Map1 = File.ReadMap(fPath,filename)                  ' import the Map values from external storage
   '
   lookup_value = parameter_value                     ' pas the parameter value to the variable
   Log("lookup_val = "& lookup_value&" "&parameter_value)   ' debug line
   Log("table size: "&Map1.Size)                     ' debug line
   For i = 0 To Map1.Size - 1                        ' begin searching the table 
      lookup_vector = Map1.GetKeyAt(i)                 ' the Map key at position (i)
      Log("index:"&i&" lookup_vector : "&lookup_vector)   ' debug line
      If  lookup_vector >= lookup_value Then            ' look for value greater than lookup_value (column 1)
         Log("greater value showed up at row:"& i)      ' debug line
         lookup_post = lookup_vector - lookup_value      ' diff to upper value
         lookup_pre  = Map1.GetKeyAt(i-1)             ' previous value
         lookup_pre  = lookup_value -  lookup_pre      ' diff to lower value
         If   lookup_post > lookup_pre Then            ' now check which one is smaller (closer to lookup_value)
            result_vector = Map1.GetValueAt(i)         ' and 
         Else                                 ' return the respective Key Value
            result_vector = Map1.GetValueAt(i-1)      ' accordingly
         End If
         Exit                                  ' exit this loop
      End If
    Next
End Sub

I will try approaching also via SQL using the Cursor.Position example to get my values.

 

[GabrielM]

Converted the subroutine above to use SQL, so I do not have to write all that table elements lot at run time.

Sub LookupTable(parameter_value As Double)  
   Dim lookup_pre, lookup_post As Double
   Dim Cursor1 As Cursor
   Cursor1 = SQL1.ExecQuery("SELECT col1, col2 FROM table1")
   lookup_value = parameter_value                      
   For i = 0 To Cursor1.RowCount - 1                   
      Cursor1.Position = i
      lookup_vector = Cursor1.GetDouble("col1")                     
      If  lookup_vector >= lookup_value Then
         lookup_post = lookup_vector - lookup_value    
         Cursor1.Position = i - 1
         lookup_pre  = Cursor1.GetDouble("col1")           
         Cursor1.Position = i
         lookup_pre  = lookup_value -  lookup_pre    
         If   lookup_post > lookup_pre Then          
            result_vector = Cursor1.GetDouble("col2")
         Else
             Cursor1.Position = i - 1
            result_vector = Cursor1.GetDouble("col2")
            Cursor1.Position = i
         End If
         Cursor1.Close
         Exit 
      End If
    Next
End Sub

I will stay with the SQL way of approach.

I have to look for a way of moving the mysql.db file from DirAssets to DirDefaultExternal after installation to device as for some reason when I tick the "Can install to External storage" my i5700 does not like it installed there.

 

[Devan]

Hi GabrielM,
Good Morning. I need to do Lookup table with 200 values in column 1 & 2.
1. How to export CSV file into SQL
2. Did your Lookup Table work fine. If yes can you pls share the code.
Really appreciate that.
Thank you.

 

[DonManfred]

This is an old thread. Start a new one for you issue giving enough details (Code)

 

[Devan]

Hi DonManfred,
Thanks buddy for your help.
will do that.
Cheers