I would like to share an example of function that extracts rows from a data table based on set value in selected columns:
VLOOKUPdataparse.sm (17,57 KiB) descargado 1278 vez(es).

I do need help with an interesting behaviour of a matrix definition:

If an element of a defined matrix contains a user defined function with an undefined parameter it may inthrow an error similar to here (same SMath file as above):



I would expect the function with undefined parameter to return itself instead of giving an error code.

Very nice

I think the issue comes out because there isn't a way to stop the preprocessing in user-defined functions when you pass an unknown (and an unknown is not a valid input).

A workaround that might be useful even to avoid the use of other functions is to use a string as input in the second row, and a check in your lookup like this:

Davide,

Is the a reason why function like findstr() does not return itself when not all variables are defined? Similar to this:
http://en.smath.info/forum/yaf_postsm36158_cases-in-definition-of-a-function.aspx#post36158

Also I am not sure if this is similar, but after I modified my excel_IO() function to return FALSE if undefined variables were present it would preprocess correctly with undefined variables when passed as an argument to a user defined function (you and I had an email discussion about this).

P.S.: the reason why a more general solution is desired is because a one might want to implement a more complex boolean check, than <>=!

Wrote

Is the a reason why function like findstr() does not return itself when not all variables are defined? Similar to this:
http://en.smath.info/forum/yaf_postsm36158_cases-in-definition-of-a-function.aspx#post36158

It is by design. However even returning itself, you will have always var=findstr(...) -> false -> ... (it doesn't prevents the strcont() function to be evaluated)

Wrote

Also I am not sure if this is similar, but after I modified my excel_IO() function to return FALSE if undefined variables were present it would preprocess correctly with undefined variables when passed as an argument to a user defined function (you and I had an email discussion about this).

Yes, but with functions created on the canvas this isn't possible

Wrote

P.S.: the reason why a more general solution is desired is because a one might want to implement a more complex boolean check, than <>=!

You can try with this:


strcont() is defined locally to avoid the preprocessing on the canvas
try() function is added because when you use el(...) inside the lookup function, all the elements of the condition matrix are evaluated -> you have to allow any type of input as first argument or let it fails silently (because strrep requires strings); note that if the selected element of the condition matrix is the one that contains strcon() with a wrong type as first argument, the error string will trigger an error on bool#:eval(bool#*operand#), because operand# will be a string.

Alex,

The attached proposal is for fixed "credit".
It might be ranged via bolean.

Jean

Alex VLOOKUPdataparse.sm (33,69 KiB) descargado 1330 vez(es).

Wrote

Alex,

The attached proposal is for fixed "credit".
It might be ranged via bolean.

Jean

Alex VLOOKUPdataparse.sm (33,69 KiB) descargado 1330 vez(es).

Thanks, Jean. I havent used "truth tables" yet; they seem to work quite well.

Is there a quicker way to assemble a SMath matrix from rows than stack()?

In vb.net I can assemble 13x10000 SMath matrix in 3.3 seconds (excel_OUT), while stacking of 488 rows (13 elements each) into matrix within SMath takes 21 seconds

Thanks!

Wrote

while stacking of 488 rows (13 elements each) into matrix within SMath takes 21 seconds

Hello Alex, can you attach an example where stack it is so slow?

Alex,

It depends what you assemble, and how.
Three examples attached.

Jean

Alex Assemble Matrix.sm (32 KiB) descargado 1224 vez(es).

Wrote

Wrote

while stacking of 488 rows (13 elements each) into matrix within SMath takes 21 seconds

Hello Alex, can you attach an example where stack it is so slow?

Here it is - I use EXCEL to input 10000 x 13 matrix into SMATH - 1.2 seconds. Vlookup() loop to stack 400 rows - 15 seconds. Vlookup() loop which does not stack anything is 3 seconds.

VLOOKUPslow.sm (27,05 KiB) descargado 1246 vez(es).
DATA.xlsx (1,61 MiB) descargado 1001 vez(es).

Try this; basically removes all stack() calls to build the matrix after you check all the conditions. On my notebook is 50% faster than your.

VLOOKUPfast.sm (31,5 KiB) descargado 1386 vez(es).

Wrote

Try this; basically removes all stack() calls to build the matrix after you check all the conditions. On my notebook is 50% faster than your.

VLOOKUPfast.sm (31,5 KiB) descargado 1386 vez(es).

Much better, thanks.

Could you explain how does the following exactly work:

resultrng#:el(data#,matches,range(1,cols(data#)))

Wrote

Much better, thanks.

Could you explain how does the following exactly work:

resultrng#:el(data#,matches,range(1,cols(data#)))

You're welcome

It is this feature; in the 2nd argument of el(3) there are all the rows I want from first argument's matrix, as 3rd argument all the columns. The result is a matrix of the elements that fits both the requirements, in the given order.

thank you for sharing these function, lookup is another essential features of spreadsheet and could be better if it has to be include in standard distribution.

from vlookupfast.sm modification, may it be simplified for such a situation below. in case condition only use boolean 'equal to' as the same in any spreadsheet function.

Wrote

lookup is another essential features of spreadsheet and could be better if it has to be include in standard distribution.

There area dozen of lookup. If so many for so many applications.
Be more specific c/w *.sm document.

... visit this intelligent/advanced lookup.

Utilities Matrix Locate Array1 Array2.sm (70,25 KiB) descargado 1281 vez(es).