PDA

View Full Version : a lookup using Xbasic


ABC123

Aprado
08-08-2011, 02:04 PM
First, I am new in alpha Five, I am using V10.
I have 2 tables, in the first I have all the field related to a Employee and in the second table I have 2 fields, the first field(Valor acumulado) is a value which I have to find and then use the second field( Valor normalizado)
I solve it by using Xbasic and it works but I want to know if using the Lookup with an external table it's better.

I tried it but allways got error .

Fieldx = Lookup("table2","variable_1 = valoracumulado","valornormalizado")
!!! for me is to look in table 2, the value of the table1 equal to a field in table 2 and if its found then return the value from table 2 to a fieldx.


thanks in advance for all your help, and sorry if my english is not so good .

Angel(Saltillo, Mexico)

dim tbl as p
dim tbl2 as p
dim varnormalizada as N

'Read in a sequential way all the first table
tbl = table.open("datos_preguntas")
tbl.fetch_first()
While .not. tbl.fetch_eof()

'look in the secod table for the value of Variable_1 which in in the table 1
tbl2 = table.open("Tabla2")
tbl2.fetch_first()
While .not. tbl2.fetch_eof()
If tbl.variable_1 = tbl2.valoracumulado then
varnormalizada = tbl2.valornormalizado
end if
tbl2.fetch_next()
end while
tbl.change_begin(.t.)
tbl.variable1norm = varnormalizada
tbl.change_end(.t.)
tbl2.close()

tbl.fetch_next()
end while
tbl.close()

Stan Mathews
08-08-2011, 02:19 PM
Lookup("table2","variable_1 = valoracumulado","valornormalizado")


If valoracumulado is numeric you want


Lookup("table2","variable_1 = "+alltrim(str(valoracumulado)),"valornormalizado")


If it is character you want


Lookup("table2","variable_1 = "+quote(valoracumulado)","valornormalizado")


Take a look at the examples in the wiki entry (http://wiki.alphasoftware.com/LOOKUP+Function) and follow the See Also links near the bottom.

Aprado
08-08-2011, 03:05 PM
Lookup("table2","variable_1 = valoracumulado","valornormalizado")


If valoracumulado is numeric you want


Lookup("table2","variable_1 = "+alltrim(str(valoracumulado)),"valornormalizado")


If it is character you want


Lookup("table2","variable_1 = "+quote(valoracumulado)","valornormalizado")


Take a look at the examples in the wiki entry (http://wiki.alphasoftware.com/LOOKUP+Function) and follow the See Also links near the bottom.

Aprado
08-08-2011, 03:13 PM
Hi, Stan thanks for answer.
IF the field is character then you use alltrim but If it's numeric then you use Lookup("table2","variable_1 = "+quote(valoracumulado)","valornormalizado")
or I am wrong? the word "quote" you have to type it or you must use " instead.

I make all this questions because it didnt work
and the last question is the value than you get from table 2 it's going to pass to the valornormalizado than it's in the firts table
do you have to describe only Table2 or table2.dbf
thanks for all your help

Stan Mathews
08-08-2011, 03:36 PM
IF the field is character then you use alltrim but If it's numeric then you use Lookup("table2","variable_1 = "+quote(valoracumulado)","valornormalizado")
The reverse of this statement.

Experiment with the AlphaSports customer table until you have the basics learned.The code below was developed in the interactive window.

varname = "graham"
varcredit = 3
?"lastname = "+quote(varname)
= lastname = "graham"
? lookup("customer","lastname = "+quote(varname),"firstname")
= "Michael "
? "creditrating = "+varcredit
= "creditrating = 3"
? lookup("customer","creditrating = "+varcredit,"firstname")
= "Sally "

Aprado
08-08-2011, 10:54 PM
Hi Stan, finally I solve the problem with the information you sent me and the links, I had to study and tried several options and at the end the sentence than works is :

lookup("tabla2","valoracumulado = "+s_quote(tbl.variable_1),"valornormalizado")

The field Variable_1 is numeric so the quote doesnt work for this data type and in the code you sent me you had an extra "
Lookup("table2","variable_1 = "+quote(valoracumulado)","valornormalizado") in====>> +quote(valoracumulado)"
but after several efforts it works
thanks for your your help,