Replicating R Code (making upper triangle = NA)

[isayev_alex156]

Hello,

I am attempting to replicate R code in Alteryx.  The code is turning the upper triangle of the a correlation dataframe into null values.  The code is below.  And what it essentially does is described in the example below.  Does anyone know what the closest method to replicating this would be in Alteryx.  I am assuming it will have to do with the MultiField Formual node, but wondering what other ideas exist.

Code:

WholeCor[upper.tri(WholeCor, diag = TRUE)] <- NA

Example of what code does:

Type 1 Type 2 Type 3  Type 4

   1          5          6          8  

   3          1          7          7

   2          9          1          2

   5          9          6          1

After Code:

Type 1 Type 2 Type 3  Type 4

   NA        NA       NA        NA  

   3          NA        NA        NA

   2          9          NA        NA

   5          9          6           NA

Thank you in advance!

[jdunkerley79 · Accepted solution]

There is probable a simpler way but the first method that occurs is:

- Add Record ID

- Transpose the data

- Add A FieldID using a Multi Row Formula

- Use a simple formula to null those records where FieldID >= RecordID

- Cross tab the result back into a matrix form

- Finally reapply the original names using a dynamic rename.

Sample attached

[NeilR]

An alternative...

[isayev_alex156]

Thank you,

I had to add a field that turns the input into the following:

Name   Type 1 Type 2 Type 3  Type 4

Type 1      1          5          6          8

Type 2      3          1          7          7

Type 3      2          9          1          2

Type 4      5          9          6          1

I'm trying to accomplish the same result but have it uneffect the field "Name", but I do not know how to properly adjust the code in the multirow formula to reflect that.  

May you please advise how I can get the same result as in your sample, but include the field called name without the result effecting that field?  I attached what is currently happening, it is not the desired result.   

[jdunkerley79]

Just a couple of small changes needed.

In the transpose tool, add Name to the key fields.

In the cross tab tools need to add Name to the grouping.

Attached updated sample.

[isayev_alex156]

Thank you so much,

Learning a ton about Alteryx!