Function VLOOKUP in Excel: which is the equivalent in REV?

[Daross]

Hi,
I'm continuing the conversion of my spreadsheets in Revolution

In the spreadsheet I have this:field E5 where I enter the number of storeys
and field M13 where the formula makes the value searched in the table (http://picasaweb.google.com/pannellirad ... 6660325858) through the formula "= VLOOKUP (E5, L39: M78, 2)"

In Revolution I made in the relevant fields (field "Storey" and field "ValueStorey") and table (I use tablefield with two columns: column "Storey" and column "Value") but to write the script I do not know where to start.
How can I do?
Thanks.

[Janschenkel]

You can do this using the 'lineOffset' function, and the knowledge that columns are separated by tabs. Assuming that you're using a DataGrid object:

Code: Select all

on mouseUp 
   put the dgText of group "StoreyValueGrid" into tText
   put lineOffset(field "Storey" & tab, tText) into tLineNumber
   if tLineNumber > 0 then
      set the itemDelimiter to tab
      put item 2 of line tLineNumber of tText into field "ValueStorey"
   else
      answer "Could not find it"
   end if
end mouseUp

By appending the tab delimiter to the first parameter of the lineOffset function, we ensure that we get exactly that line, and not another line that happoens to start with the same characters (think: 'foo' and 'foobar' start with the same 3 characters, but you really want 'foo' and not 'foobar' when you look for 'foo')

HTH,

Jan Schenkel.

[Daross]

Hi Jan,

I have tried with your script (I also tried to change it) but does not work

Sorry my description was unclear

This is my stack:

Button "Calculate"

field "Storey" where I put the number of storey

field "ValueStorey" where the script puts the corresponding value of the number of storey

Datagrid "StoreyValueGrid" composed of a column "Storey" and column "Value" where the script should check the value entered in the "Storey" and put corresponding value in the "ValueStorey"

This is the stack:http://picasaweb.google.com/pannellirad ... 6801103154

Thanks in advance

[Janschenkel]

I understood what you were trying to do - but the script I posted works correctly for me. Perhaps you could post the actual stack online somewhere, and we can determine why it's not behaving as expected.

Jan Schenkel.

[Daross]

Hi Jan,

you can see the stack here:

http://files.me.com/umth

Thanks

[Janschenkel]

Found the problem: there is another group named "StoreyValueGrid" on your card, and it's not a data grid (it's just a few pixels high and wide, near the top of the card)
Use the Inspector palette or the Application browser to find and delete that group, and the script will work.

HTH,

Jan Schenkel.

[Daross]

Very thanks Jan and

sorry if I made you lose time

[Janschenkel]

No problem at all - happens to newbies and veterans alike - I'm sure you'll check the Application browser religiously from now, when something doesn't work as expected. It's the way we learn: by making lots of mistakes and hopefully drawing the right conclusions from those errors

Jan Schenkel.

[Daross]

Hi,


I'm finishing my stack, and I have a problem:

if I put a number in the field, Storey, and that is not listed in the column of

datagrid Storey StoreyValueGrid, the search produces no results (for

example if I enter the number 26).

The solution would be to add the rows with numbers and values, but

should I create a datagrid huge!

How should I do?

For now, to run the stack, I wrote the following code:

Code: Select all

on mouseUp
    if fld "Storey" <= 15 then
      put 0.43 into fld "ValueStorey"
   else
 if fld "Storey" <= 16 then
      put 0.43 into fld "ValueStorey"
   else
       if fld "Storey" <= 17 then
      put 0.42 into fld "ValueStorey"
   else
       if fld "Storey" <= 18 then
      put 0.42 into fld "ValueStorey"
   else
       if fld "Storey" <= 19 then
      put 0.41 into fld "ValueStorey"
   else
       if fld "Storey" <= 20 then
      put 0.41 into fld "ValueStorey"
   else
           if fld "Storey" <= 21 then
      put 0.4 into fld "ValueStorey"
   else
           if fld "Storey" <= 22 then
      put 0.4 into fld "ValueStorey"
   else
           if fld "Storey" <= 23 then
      put 0.39 into fld "ValueStorey"
   else
           if fld "Storey" <= 24 then
      put 0.39 into fld "ValueStorey"
   else
       if fld "Storey" <= 25 then
      put 0.38 into fld "ValueStorey"
   else
       if fld "Storey" <= 30 then
      put 0.36 into fld "ValueStorey"
   else
       if fld "Storey" <= 35 then
      put 0.35 into fld "ValueStorey"
   else
       if fld "Storey" <= 40 then
      put 0.34 into fld "ValueStorey"
   else
       if fld "Storey" <= 45 then
      put 0.33 into fld "ValueStorey"
   else
       if fld "Storey" <= 50 then
      put 0.32 into fld "ValueStorey"
   else
       if fld "Storey" <= 60 then
      put 0.31 into fld "ValueStorey"
   else
       if fld "Storey" <= 70 then
      put 0.3 into fld "ValueStorey"
   else
       if fld "Storey" <= 90 then
      put 0.29 into fld "ValueStorey"
   else
       if fld "Storey" <= 100 then
      put 0.28 into fld "ValueStorey"
   else
       if fld "Storey" <>125 then
          put 0.27 into fld "ValueStorey"
       end if
    end if
       end if
    end if
       end if
    end if
       end if
    end if
       end if
    end if
       end if
    end if
 end if
 end if
       end if
    end if
       end if
    end if
       end if
    end if
    end if
end mouseUp

[Klaus]

Buongiorno Daross,

I have no idae about your actual problem, but can give you a hint how you can make you last script a bit more readable (readable at all ):

Code: Select all

on mouseup
     put fld "Storey" into tStorey

     switch 
     case tStorey <= 16 
     ## This also includee <= 15!
         put "0.43" into fld "ValueStorey"
     break

     case tStorey <= 18 
        put "0.42" into fld "ValueStorey"
     break

     case tStorey <= 20 
        put 0.41 into fld "ValueStorey"
     break

     case tStorey <= 22
        put "0.4" into fld "ValueStorey"
      break
      ### etc.
      ### You get the picture :)

   end switch
end mouseup

Best

Klaus

[Daross]

Hi Klaus,

I should edit the script of Jan:

You can do this using the 'lineOffset' function, and the knowledge that columns are separated by tabs. Assuming that you're using a DataGrid object:

Code: Select all

on mouseUp 
   put the dgText of group "StoreyValueGrid" into tText
   put lineOffset(field "Storey" & tab, tText) into tLineNumber
   if tLineNumber > 0 then
      set the itemDelimiter to tab
      put item 2 of line tLineNumber of tText into field "ValueStorey"
   else
      answer "Could not find it"
   end if
end mouseUp

By appending the tab delimiter to the first parameter of the lineOffset function, we ensure that we get exactly that line, and not another line that happoens to start with the same characters (think: 'foo' and 'foobar' start with the same 3 characters, but you really want 'foo' and not 'foobar' when you look for 'foo')

HTH,

Jan Schenkel.

Here you can see, the stackhttp://picasaweb.google.com/pannellirad ... 01103154

[Daross]

no answer? Sorry, I explained bad
In my stack I make:
one button
two fields "Storey" "ValueStorey"
one datagrid "DataValueStorey" (is a mini-database) composed so:

Storey Value
15 0.43
16 0.43
17 0.42
18 0.42
19 0.41
20 0.41
21 0.4
22 0.4
23 0.39
24 0.39
25 0.38
30 0.36
35 0.35
40    0.34
etc.

The script is:

Code: Select all

on mouseUp 
   put the dgText of group "StoreyValueGrid" into tText 
   put lineOffset(field "Storey" & tab, tText) into tLineNumber 
   if tLineNumber > 0 then 
      set the itemDelimiter to tab 
      put item 2 of line tLineNumber of tText into field "ValueStorey" 
   else 
      answer "Could not find it" 
   end if 
end mouseUp

The problem:
if I put a number in the field "Storey", and that is not listed in the column "Storey" of datagrid "StoreyValueGrid", the search produces no results
For example if I enter the number 26 ( is not listed in the column) the script should be put in the field "ValueStorey" 0.36.
How can I do?
Thanks in advance

Who wants to download the stack is here
http://files.me.com/davide.rossato/0048ls

[Klaus]

Buongiorno Daross,

well, obviously you cannot use "lineoffset" in that case.
I would use the "enhanced" version of your initial script


Best

Klaus

[bn]

Davide,

try

Code: Select all

on mouseUp
    put field "Storey" into tSearch

    -- lets do a little errorchecking
    if tSearch is empty then exit mouseUp
    
    -- check if it is a number at all
    if tSearch is not a number then
        answer "Storey must be a number"
        exit mouseUp
    end if
    
    
    put the dgText of group "StoreyValueGrid" into tText
    
    set the itemdelimiter to tab
    
    -- check if number is smaller then smallest Storey
    if tSearch < item 1 of of first line of tText then
        -- do what is appropriate
        answer "Storey is too small"
        exit mouseUp
    end if
    
    -- check if number is higher then highest in datagrid
    if tSearch >  item 1 of last line of tText then 
        put item 2 of last line of tText into field "ValueStorey"
        exit mouseUp
    end if
    
    
    
    put 0 into tCounter
    repeat for each line aLine in tText
        add 1 to tCounter
        if tSearch = item 1 of aLine  then
            put item 2 of aLine into field "ValueStorey"
            exit  repeat
        end if
        if  tSearch < item 1 of aLine then 
            put item 2 of line tCounter -1 of tText into field "ValueStorey"
            exit repeat
        end if
    end repeat
end mouseUp

in your button. It works for me, but I did only limited testing.
regards
Bernd

[Daross]

Klaus, thank you, now I use the structure if then else most cleanly

Bernd, wow! your script works perfectly!

Danke!

A question; what is aLine?