Hello everyone,
I have a new question for you that should be straightforward. I'm wondering how write a program to add additional rows to a matrix, where the values of the elements are calculated according to a formula using existing data within the matrix.
The attached Excel file includes a tab with input for the ReadExcel function, as well as a tab showing the desired output by a writexcel function. Essentially, the matrix is a list of node point IDs with force vectors due to six load cases. Each node ID will have the same number of load cases and the input will be always sorted by LoadCase column. I would like to add new rows for each node ID, namely 7 OPEPOS and 8 OPENEG, using a specific formula which pulls its input from the existing data within the matrix.
Input for the readexcel function:
Desired output by the writexcel function:
Rows 8, 9, 16 and 17 should be added by the formulas:
FR is the force resultant.
Just as a note, I have currently MathCAD Prime 8 and unfortunately I don't have access to p9.
ACCEPTED SOLUTION
Accepted Solutions
Hello LucMeekes,
I started with the program as suggested and my Prime worksheet is attached as well as the raw excel data for the input.
Please disregard the previous excel input file and work with the attached excel file "Input and Desired Output v2.xlsx".
I was able to read the raw data and then sort the matrix in groups of NodeIDs.
I can take it from this:
To this:
Where I need the help is to insert two rows for each group with the calculation of OPEPOS and OPENEG for FX, FY and FZ columns.
FR is the resultant force calculated by the square root of the squares (FX^2+FY^2+FZ^2)^1/2.
The desired output should be like this:
The formulas for OPEPOS and OPENEG are in the MathCAD worksheet so you don't need to type them from scratch.
Thanks in advance for the support.
Here are my 2 cents, but I used your original Excel input file.
The solution is unlikely to win a contest for beauty and elegance, but so what ...
To use the new unsorted Excel Input file, you would first have it to sort for column F and then for column A.
You may do it in Prime, too, but you have to take care because the built-in "sort" function would not leave the order of the sorted column F intact when you sort for column A.
There were posted sort functions here quite a while ago which would do so, though.
But you may also use the grouped record you already created and stack them so you get the needed single matrix.
You may also use a command like the following
But I just notice that your new Excel file is missing some of the columns which were present in the first one you posted, so you would have to change my ProcessData( ) function accordingly - guess you will see where changes are needed.
Attached file is in format Prime 9
Hello @Werner_E and @LucMeekes
The program worked.
I had to adjust the number of columns for the submatrix when reading the Input (from cols(Input)-1 to cols(Input) )and then I created the header and finally made the Data:=stack(header,data) before passing it to ProcessData(Data).
The newData matches the desired output. I also tested it by adding another group of nodes with the same six load cases. I will expand the program for more load cases that will be input for other formulas. If I run into some issues, I'll let you know.
Also, just for my understanding, please explain me what happens here:
Also, just for my understanding, please explain me what happens here:
You may have noticed that the algorithm used for "csort" does not preserve the order of the rows with the same "Area" letter when you first sort the matrix for the "LoadCase" column and then for the "Area column". There are algorithms which do preserve the order, but the built-in doesn't.
One way to cope with that problem is seen in the picture. The concat command creates strings by concatenating the Area letter and the LoadCase. So something like "A1 GR" or "B4 T3" is created. Because I want to concat the whole vectors I had to vectorize (the arrow over the expression) the command.
This new column is put in front of the data matrix via the augment function and this new matrix is now sorted for this first column. This creates the order we need - primary key "Area" column and secondary key "LoadCase" column.
After sorting the first column is removed using the submatrix command. It may be a little bit shorter if the first column is removed using the trim command.
data:=trim(csort(augment(concat(....)) ^T,0)^T
should work as well (^T denotes matrix transposition which is necessary because trim will only remove rows)
