topic Re: Matching values in a row of a table in Mathcad

Matching values in a row of a table

IRarch — Thu, 03 May 2018 13:29:32 GMT

Hi,

I have an extensive table with 5 columns (0 to 4) and more than 1000 rows!

I want to select the values from dorp down lists for the first 4 variables (each variable in one of the first 4 columns). Then I want to find the row that contains all the exact values I selected previously so to find the value of the 5th column (4) at that row (the row with the exact values). No row in the entire table repeats.

Attached is the table in an excel file I will be using.

I would like to be able to select the Height, Width, HFX Condition, Bolt Diameter and then get the corresponding Seismic Allowable Shear value.

Any ideas? Thanks!

Re: Matching values in a row of a table

Werner_E — Wed, 23 Aug 2017 23:13:41 GMT

What you are looking for should be able to do with a routine from Richards fine collection of scriptable components (which I can't find now in this new, horribly forum) using Mathcad 15, bur not with Prime.

Your list includes a lot of duplicates concerbning the first four columns and the bolt diameter it "1.125 STD" throughout.

So which result do you expects with Height=78, Width=9, HFX Cond = Concrete 2500psi and bolt d. =1.125 ?

Re: Matching values in a row of a table

RichardJ — Wed, 23 Aug 2017 23:28:21 GMT

Like this?

Re: Matching values in a row of a table

IRarch — Wed, 23 Aug 2017 23:44:13 GMT

Hi @Werner_E,

You are right, I forgot to add one more column. Plese see revised file. The table is much more complex but I was trying to simplify it. The idea is that no row repeats in a table of 1000+ rows.

I'll appreciate if you do find the routine or if you can send me on the right direction.

Thanks!

Re: Matching values in a row of a table

RichardJ — Thu, 24 Aug 2017 00:04:10 GMT

Does this not do what you want?

Re: Matching values in a row of a table

Werner_E — Thu, 24 Aug 2017 00:14:36 GMT

I guess Richard already did the job.

Please note that in your Excel sheet in the first column (Width) some entries like 93 3/4 are numerical (formatted as fraction), but others, like 92 1/4 are simply text!. If you read in the data via READEXCEL, this is causing troubles (I know, I was working on a similatr sheet Richard provided).

Richard was copying the Excel data directly to a Mathcad table and this seems to convert the text 92 1/4 automatically to a number, Drawback is that you can't just change the Excel table, you would have to copy the data again to the Mathcad table. Benefit is that the Mathcad sheet is self contained and does not need the Excel sheet anymore.

Re: Matching values in a row of a table

IRarch — Thu, 24 Aug 2017 17:55:58 GMT

Hello @RichardJ

I am still woriking on it with your example HFASU.

I think this will do it. I will show you as soon as I get done with it.

Thank you so much for all of this.

Re: Matching values in a row of a table

IRarch — Thu, 24 Aug 2017 17:57:36 GMT

Hi @Werner_E

Once more thanks! I am fixing this right now...

Re: Matching values in a row of a table

IRarch — Thu, 24 Aug 2017 19:03:10 GMT

Hi @RichardJ

What I have so far is that I am getting a matrix with the rows where the HardyFrame.Width, HFX.InstallationCondition, and Bolt.Diameter.Grade match but I am also getting a 0 in the matrix where on row 0 is not supposed to match all three parameters. There seems to be something wrong where I am gettting 0's.

PS. I changed the StripDuplicateRows program where I made the submatrix (=) instead of (not equal).

Im trying to attach the mathcad file but I get the following error message

Re: Matching values in a row of a table

Werner_E — Thu, 24 Aug 2017 19:09:04 GMT

This horrible new forum doesn't have its act together.

Some of us are able to directly upload Mathcad files, but most of us get the very same error message you experience.

Workaround is to put the file in a zip archive and upload the zip file 😞

Re: Matching values in a row of a table

IRarch — Thu, 24 Aug 2017 19:10:50 GMT

File compressed

Thanks @Werner_E

Re: Matching values in a row of a table

Werner_E — Thu, 24 Aug 2017 19:53:21 GMT

Why did you changed Richards "not equal" to "equal".

What do you think this new routine is doing?

If your intention was to filter for the values which are present in two vectors, thats sure not what this routine can do.

See in Richards sheet one way how it could be done.

Re: Matching values in a row of a table

IRarch — Thu, 24 Aug 2017 20:02:15 GMT

I changed it because we want to see the duplicate rows. Not sure why I still get the 0's though.

Re: Matching values in a row of a table

Werner_E — Thu, 24 Aug 2017 20:13:32 GMT

Duplicate rows?

Do you mean values which are present in both vectors? How should the routine find that out if you provide just one stacked vector as argument?

Or what exactly should the routine do with the vector argument? Only showing the numbers which occur more than once?

Re: Matching values in a row of a table

Werner_E — Thu, 24 Aug 2017 20:22:13 GMT

If my first guess wa right, you may be looking for something like this

I don't investigate why your modified routine is not doing this as the routine was designed by Richard to do soemthing completely different one.

BTW you can use "intersect" also for sorting a vector and removing duplicate values if you provide the same vector twice as argument.

Re: Matching values in a row of a table

Werner_E — Thu, 24 Aug 2017 20:41:11 GMT

Addendum:

The function "intersection" returns the number 0 and not a vector, if no common value is found.

This can be a problem if you nest that function to find the intersection of more than two vectors

To cope with this, add one line of code on top:

This should cure that problem

Re: Matching values in a row of a table

RichardJ — Thu, 24 Aug 2017 21:00:09 GMT

@IRarch wrote:
PS. I changed the StripDuplicateRows program where I made the submatrix (=) instead of (not equal).

Why did you change it? If you change it then it will not work properly.

Re: Matching values in a row of a table

RichardJ — Thu, 24 Aug 2017 21:08:04 GMT

@IRarch wrote:

I changed it because we want to see the duplicate rows.

In the table there are no duplicate rows (I take your word for that; I have not looked). I strip the duplicate rows from the vectors because the vectors are used to fill the listboxes, and there's no point in having duplicate entries in the listboxes. That function is designed to strip duplicate rows from matrices, and using it for vectors is kind of overkill, but I already had it. You should not change it. If you want to see the duplicate rows, just don't remove them in the first place!

Re: Matching values in a row of a table

Werner_E — Thu, 24 Aug 2017 21:54:06 GMT

I think his intention was to misuse your routine for a different purpose. He is not using your scripted components which filter the possible values from his data list and have to avoid duplicates. He is using web controls with hard coded entries. So he does not need your routines to eliminate the duplicates.

He has only three of his four or five input values implemented until now and so there will be more than one row where all those three values match.

And I guess he simply wanted a routine which shows the indexes of those rows and he thought, if he simply changes the "not equal" to "equal" in your function, he would get that.

My "intercept" routine maybe would do what he tried to achieve (if my crystal ball worked OK)

Re: Matching values in a row of a table

IRarch — Fri, 25 Aug 2017 22:50:01 GMT

Hello @Werner_E @RichardJ

Please see the solution so far.

A problem I am encountering is that the option of a 12in Width and 11ft element does not happen but Z0 is telling me that on the 3^rd row those two happen when in the table provided are not.

The option of 12in Width and 79.5in Height does give me the right output in USE. What I would expect with the 12in and 11ft.

Please let me know what you think about that problem…