MB Affinity tool changes / normalizes the column headers

[Roche]

Good day everyone

I have noticed that the MB Affinity tool changes the item names (product IDs) on which it creates a co-occurrence matrix. (Attached is an example of how it changed the product IDs)  Every character in the IDs is changed to an underscore (_).  It also adds an X if the ID is a number or if the ID starts with numbers. 

From the searches I have done thus far, I do not see any information on this.  Does someone perhaps know how I can prevent this automatic change of the names?  Or how I can change the names back to what it originally was, without needing to physically go about matching 100s or 1000s of product names for a find replace tool?

I would also like to ask if anyone who worked (works) with complex product IDs (such as in the example) has experienced this issue of changed names?

Thank you!

[mceleavey]

Hi @Roche ,

this is due to the tool using the crosstab tool, which replaces spaces and special characters with underscores.

If you post your workflow with a single row of data (mock data) I can build it for you to ensure the original names are retained.

M.

[Roche]

Hi Mceleavey

Thank you for letting me know about this.  I checked the workflow again.  I have used no transpose tool before the MB Affinity tool.  Before the MB Affinity tool the names remain the same.  The output of the MB Affinity tool displays the names changed as described above.  So I believe it is indeed the MB Affinity tool that changed the names.  I use the transpose tool afterwards and then it does not change the data, it displays the names as it is in the MB Affinity matrix output. 

So I need to find out how to prevent this change or have a way to configure tools after the MB Affinity tool to change the column names back to their original format.

[Roche]

However, perhaps I can just transpose the data back to the original matrix, so I will send an example, but will create something similar to the original data, with fields etc. the same.  Thank you for your help!

[mceleavey]

Hi @Roche ,

No, the Transpose and crosstab tools are within the tools themselves, hence the change.

You can simply use the dynamic rename tool to replace the new names with the old (connect the original data with the transposed headers in a column to the new output).

Let me know if you need help with that.

M.

[Roche]

Hi Mcleavey, 

Thank you, I will have a look at the dynamic rename tool. 

Rouche

[Roche]

Hi again.  I can see how I will be able to use the rename tool, but am looking for assistance with another part before I will be able to use the dynamic rename tool.  

I have a fixed column (no specific order) by which I need to sort another column.  I added record ID's to the product ID's but have not found a solution yet to be able to sort in this manner.  Attached is an example of what the data looks like.  The 'order' column is the order in which the values should be and the 'sort' column is the corresponding values with the same record IDs as a prefix.  There are many of these product IDs, so I give an example of 5 rows.  The S and O columns represent what the prefixes actually look like: the one column (sort) is in ascending order and the the 'order' column has no specific order. 

If I can sort the values according to the 'order' column, I will then be able to use the dynamic rename tool. 

Thank you for helping! 

Rouche