Matrix and NAN filtering

[GvanBoven]

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. 

[LucMeekes]

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]

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]

Hier is 'tie:

Veel plezier!
Luc

[tietjee]

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]

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" 

Error

About Werners solution

[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

[Werner_E]

---

About Werners solution

---

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

[GvanBoven]

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?

[Werner_E]

---

@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.

[Werner_E]

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:

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: