cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you know you can set a signature that will be added to all your posts? Set it here! X

Matrix and NAN filtering

GvanBoven
6-Contributor

Matrix and NAN filtering

Good day everyone,

 

I have a matrix with test results from different kinds of tests (500 x 1000 in size). 

In the first 5 columns it tells which tests are done by either a true statement or nothing. 

For calculation I want to select specific parameters from specific columns that are only done for a specific test.

 

To do that I think I need to filter make a new matrix where I only select all rows where a test is true. After that I can select the right colums for the parameters.

 

I am afraid I cannot share my original file but I made a small example of what I am trying to achieve

In the first matrix the first 3 columns represent the output if a certain test has been done. columns 4 to 6 represent the test outcomes I want to use for my calcylations. The 3 matrixes below are the desired output.

I hope someone can help me with this. 

 

10 REPLIES 10
LucMeekes
23-Emerald III
(To:GvanBoven)

I remember that the FilterNan() function isn't always as reliable as you'd expect, but that may be limited to Mathcad 15...

 

If you replace the NaN's with zero's (0) and the True's with negative one's (-1), you can use the csort() function and select column 0, 1 or 2, then use match() to find the last -1 in that column, which gives you the row index of where it's 'true'. With the total number of rows and columns in the matrix, AND that row index you can call the submatrix() function to get the exact part of the matrix that you need.

 

The attached should help...

 

Success!

Luc

GvanBoven
6-Contributor
(To:LucMeekes)

Hello Luc,

 

Thanks for your response

 

I have read some things about the FilterNAN not working proberly before Mathcad 3,0 on this forum. I am however using 3.1. That brings me to my next question about the document you uploaded. Can you save it for mathcad 3.1 so I can open it.

 

I also need to add here that in the real file not all data fields are filled either. That was a mistake on my part making the example. In the real sheet I also have tests that do not give every paramater. They also show up after import as NaN

 

 

LucMeekes
23-Emerald III
(To:GvanBoven)

Hier is 'tie:

 

Veel plezier!
Luc

tietjee
14-Alexandrite
(To:LucMeekes)

Couple of comments.

 

You can use the READEXCEL command, if the source file is an Excel file.  The READEXCEL has an option to to set blank cells to NaN, number, or a string.

 

The if, match, etc. can be used with strings.

 

The match command list the indexes for each match.  The indexes can be used to sort on columns to select the rows of interest.

 

Cheers  

GvanBoven
6-Contributor
(To:tietjee)

Thanks Luc for the file and Werner too for your suggestion. I however still encounter some issues.

 

About Luc's solution:

I tried loading in my Excel with readexcel after setting the string WAAR := -1.

Now I get an error on the select options. See the atached image below.

The error boxes state the following error: Unknown error: col_must_have_uniform_dimension_or_all_strings.

I think thats because the true in the example should have been "true" 

ErrorError

About Werners solution

 

erro1.png

 

GvanBoven
6-Contributor
(To:GvanBoven)

Oke did some more puzzling, I got Werners first solution to work. It seem that when I now select specific columns of specific tests I get the right data.

 

Tommorow Ill try to do some more tests. Thanks everyone for the help


About Werners solution

 

erro1.png

 


In the  "exponent of M you typed a simple pair of parenthesis, but it should be the column selector <...>.

You get it by typing M followed by Ctrl-Shift-C or via the menu as shown below

B.png

GvanBoven
6-Contributor
(To:Werner_E)

Yes I noticed it. Thanks anyway for pointing it out.

 

Now a colegue of mine has changed the values in the fields of the tests to true and untrue (instead of NaN).

 

How do i change the formula?


@GvanBoven wrote:

Yes I noticed it. Thanks anyway for pointing it out.

 

Now a colegue of mine has changed the values in the fields of the tests to true and untrue (instead of NaN).

 

How do i change the formula?


Hopefully its text, that is "untrue" (with the quotes.

In this case you write "match" instead of "matchNaN" and in the function parenthesis you add "untrue" as first argument.

B.png

This should do the job.

Don't have P31 installed an PTC software is silly enough not to be able in older formats, so all you get is a picture:

B.png

 

While "Match" or the various "Lookup" functions often provide reason for complaint, I am not aware that "filterNaN" would do so, too. But filterNaN would not be very useful for your problem, as it would remove every row in your matrix which contains one or more NaN's, which in your case would mean it will remove every row leaving nothing which again would result in an error.

You may user "filterNaN" if you want to omit the test columns not in question from your output, though:

B2.png

Top Tags