Need help to change the values in a matrix

[Suvasini]

Hi, I have a matrix similar to the table posted below. The first column are my origin stations and the first row is my destination stations. So while going from station A to B - there were 90 people. When I travel from station A to A, from B to B - it should be 0 and I was able to specify that. There is a 0 diagonal. Due to a requirement, all the numbers below the diagonal need to be zero too. So from station B to A, instead of 38 it should be 0 and likewise for all the cells below the diagonal. How can I do that?

	A	B	C	D	E
A	0	90	30	17	55
B	38	0	98	15	53
C	52	74	0	17	13
D	100	58	29	0	78
E	78	84	69	28	0

[DavidP]

Hi @Suvasini 

Would something like this work?

[Suvasini]

Hi @DavidP (16 - Nebula)

Thank you for the quick response. Appreciate it, I tried the solution you sent, but the stations are headers itself so there is no F1,F2,F3 etc in my workflow. Also when there is a 0 value above the diagonal it doesn't seem to work as it makes all values below it zero. Would request your help again. I have attached a workflow with the data set close to what I'm working with. The idea remains the same that below the A-A, B-B diagonal (which is already 0), cells below it have to be zero too.

[DavidP · Accepted solution]

Hi @Suvasini 

Let me know if this works for you and I can explain a little more of my methodology.

[Suvasini]

Yes, this was the output needed. Thanks alot. Kindly explain the methdology.

[DavidP]

Excellent!

For my plan to work, it's important to preserve the row and column order, so I insert a Record ID and then use that to rename the columns with their relevant number. I can then use the Transpose tool to get all the values in a single column. I picked an arbitrary number of 0.000001 to assign the diagonal where Row ID = Column ID (or Origin = Original Column Name)

Using a sort tool, I can identify the rows below the diagonal for each column and then also set their value to 0.000001 with the multi-row formula tool (note that I use the group by functionality here)

Now I can re-sort the transposed rows before using the CrossTab tool to flip the data back to the original format.

Finally, I can restore the original column names with a Dynamic Rename tool.

Please let me know if you have any questions.