Find and Replace function + Generate rows function

[NairutiPatel]

Hi all, 

I am trying to use the 'Find and Replace' function to append a field to my existing records. Please see attached excel as reference. 

I want to append the 'terms' column to my original file using the 'category' column as reference. Than, I want to generate additional rows in my original file, depending on how many terms there are. So, for 'metastatic' two additional rows should be generated. I have created a map key file like the one attached. However, the field are not appending to my record - Alteryx has created another column on my original file titled 'Category2' with [Null] for all the records. Also, when i use the 'Generate Rows' functions with conditional expression as RowCount <= [Number of Terms], I'm getting a Parse Error at char(14): Unknown variable 'Number of Terms'. 

I'm not too sure what i'm doing wrong here. Many thanks in advance!

[grossal · Accepted solution]

Hi @NairutiPatel,

I am not sure what exactly went wrong in your workflow and decided to create an example for you.

Result:

I'll attach the example. Let me know if this helped you!

Best

Alex

[NairutiPatel]

Thank you for the example workflow. If I now want for example, to separate out the terms for say metastatic so that the 1st duplicated row corresponds to Stage III and the 2nd duplicated row corresponds to Stage IV. How would I do that?

[grossal]

In this case, I wouldn't use the Generate Rows tool, but the Text-To-Columns.

Result:

We use the Text-To-Columns to split the Terms by Comma to Rows.

Settings:

Updated Workflow attached. I also added a Data Cleansing Tool to remove some blank characters.

Best

Alex

[NairutiPatel]

That was very helpful. Thanks a lot!