Hello everyone,
I'm encountering a problem with the attached Mathcad Prime 8 where the custom function MaxRecords(M) isn't yielding the expected results when reading imported data from the attached RAW DATA.xlsx.
The input matrix "MaxT3" passed to the MaxRecords(M) function, comprises five sets of results for each ID where there is a total of 289 IDs. I need to extract only the records with the maximum resultant force FR (column 10). The result from MaxRecords should have only 289 records, but it's currently delivering 1445 records (289x5). Could you please review and let me know where the issue lies?.
Also, the resultant matrix of maximum records should keep the same order as the matrix MainRunT3:
I tested the MaxRecords function with a smaller data set and is working fine (see snapshots below of the smaller set raw data and the corresponding MathCAD results):
Finally, please let me know an easy method to disconnect a large matrix from its formula.
The attached prime is version 8. Please keep in mind that currently I don't have access to p9.
1 ACCEPTED SOLUTION
Accepted Solutions
OK, I fixed the first part of your own MaxRecords() function (creation of vector R). The second part should work OK
But I preferred to streamline the second half a bit using the match() function.
Here is the function which, as I hope, does what you expect:
You will notice that MaxRecords2() does not return the very same result as the fixed MaxRecords(). Reason is that my MaxRecords2() choses the FIRST row if there is more than one row per ID with the same maximum force (occurs usually when the force is zero), while your approach choses the LAST one.
I had already asked in the past what the function should do in that case, but again you ignored that question.
So here is a slight modification which returns the same result as your function:
BUT ... the order of the rows in the result matrix are now NOT THE SAME as their order in the original input matrix.
But it looks to me now that this is what you expect (hopefully!).
I attach the full Prime 9 sheet along with a Prime 6 sheet (I don't have P7 and P8 installed) which just includes the function definitions. You should be able to open that sheet and copy the functions (you have to do it one after the other thanks to PTC) in your P8 sheet.
Just noticed that you wrote that the order of occurrence has to be preserved. So sorting for column 2 might not be an option.
Here is an approach which works on AllT3, does not resort and returns the desired lines. It would also work if the number of cases per ID is not exactly 5 for every ID.
In case of equal values in column 10, only the first row in M is returned.
In case you want the last row found returned, you will have to reverse the order in the j-loop (let i run from 0 to rows(M)-1.
And if you want to the function to return all lines in that case you have to change the >= to a > (and the order of the j-loop does not matter). You would get a matrix with 338 rows instead of the 289. So I guess thats not what you expect.
The attached file is in format Prime 9. So you will have to retype from the picture (or wait if someone is able and willing to provide a converted file).
And yes, I know that my function is very inefficient as of the two large nested loops. But at least I could avoid the time consuming submatrix command in the loops or the memory consuming creation of a structure as large as M. But there sure are much more efficient algorithms possible.
P.S.: I have not looked in great detail into your MaxRecords() function, but on first glance it looks to me that it would only work if the argument matrix is sorted for column 2, which your matrix AllT3 is NOT!
Its the first part of your function which should create the nested vector (a 289x1 vector with 5x19 matrices as elements)
This part of your function skips to the next element (i<--i+1) if the ID in the matrix changes without considering that there might be rows with the same ID later.
Furthermore it look like your approach would not preserve the order in which the lines with the max force value occur in the matrix (as I understood thats something you demanded).
Simple example:
Matrix M:
| ID | force |
| A | 10 |
| B | 50 |
| B | 20 |
| A |
70 |
my function would return
| ID | force |
| B | 50 |
| A |
70 |
while yours would return
| ID | force |
| A | 70 |
| B | 50 |
Yes, I thought that my function does exactly that!? Doesn't it?
Where is the problem/error?Your function MaxRec(M) is working and returning only the unique IDs but just need to keep the order as the ID first appeared in the AllT3 matrix:
Your result MaxT3b looks different then the one on my side. Did you use a different input file?
Anyway, I am referring to my picture here. ID "B69 1" is the first entry in my result vector, because the maximum force for ID "B69 1" (1195) occurs in line 17 of AllT3.
The maximum value for ID "B000 1" (its 93) occurs much later in line 867.
So preserving the order means that ID "B69 1" must be in front of ID "B000 1" as otherwise the order of the lines in the result matrix would differ from the order they occur in the input matrix AllT3!
That was the reason for my comparison of the two possible behaviors in a prior answer of mine which you still ignore:
In my opinion the behavior of my function is keeping the order intact as the rows with the maximum force appear in the very same order as they appear in the argument matrix AllT3. BTW similar to the example picture where you highlighted three rows with yellow
The second behavior which your function would show as soon as you fix the first part of your function does NOT always leave the order intact as the example shows.
OK, I fixed the first part of your own MaxRecords() function (creation of vector R). The second part should work OK
But I preferred to streamline the second half a bit using the match() function.
Here is the function which, as I hope, does what you expect:
You will notice that MaxRecords2() does not return the very same result as the fixed MaxRecords(). Reason is that my MaxRecords2() choses the FIRST row if there is more than one row per ID with the same maximum force (occurs usually when the force is zero), while your approach choses the LAST one.
I had already asked in the past what the function should do in that case, but again you ignored that question.
So here is a slight modification which returns the same result as your function:
BUT ... the order of the rows in the result matrix are now NOT THE SAME as their order in the original input matrix.
But it looks to me now that this is what you expect (hopefully!).
I attach the full Prime 9 sheet along with a Prime 6 sheet (I don't have P7 and P8 installed) which just includes the function definitions. You should be able to open that sheet and copy the functions (you have to do it one after the other thanks to PTC) in your P8 sheet.
Hi
I only have Prime 9 so you will have to copy by hand.
A simpler program to extract the maximums exploiting the fact that there are 9 load cases per record
Results in:
Cheers
Terry
Hello @terryhendicott , please adapt your proposed function to be use with the matrix AllT3. Process the matrix AllT3 as is with no preprocessing or sorting of any kind. I tried to adapt it with no success:
