parsing.. and crosstab

[Burton5555]

I've taken a field with text, and numeric values together and parsed them. The numeric values are currently a string and represent a scores.. (site1 3.5 4.5 5.8) I've parsed this and created capability_field and then score1, score2, and score3.

I've appended this from another table (company), converted score to a numeric value to build a crosstab....  my challenge is getting the scores to populate the crosstab correctly..  My value are repeating across my columns... any thoughts?

[mceleavey]

Hi @Burton5555 ,

Can you provide the original data so we can see what you were going from, and perhaps what you're trying to get to?

M.

[Burton5555]

Input table 1

Input table 2

Desired output.

thanks

[atcodedog05]

Hi @Burton5555 

Can you provide this has a excel file so that we can use it to work on logic. 

[Burton5555]

Hi,

Here are the files... thanks again for any insights.

[atcodedog05 · Accepted solution]

Hi @Burton5555 

Here is how you can do it.

Workflow:

Input 2:

1. I am splitting the columns into capabilities & values using Regex parse

2. Select tool to filter columns & rename

3. Split values into columns using text to column. I am giving root name as empty so that columns generate as 1,2...

Input 1:

1. I am setting record ID

2. Using select tool to convert record ID to string

Using dynamic rename tool I am using ID (1,2...) as key i am passing replacement headings. Which will replace 1,2... column names to company column names.

Hope this helps 🙂

[Burton5555 · Accepted solution]

very slick... thank you 🙂