Replace Header Text With Blanks

[amberdimp1]

Hi Community!  I have some data that I'm trying to finagle; however, I'm at a lost on how to replace certain text words with blanks. My flat file data exports with the below on each column header.  I want the headers to only display the actual question and not all of the contents before the multiple pipes. In excel, I would highlight row 1 and use find/replace Program|Page 1|Face to Face Survey| with blank. Is there a way to do that in Alteryx without doing it one column at a time?

Currently: Program|Page 1|Face to Face Survey|Which color was requested the most?

Need: Which color was requested the most?

Thx~

[CharlieS]

Depending on if you want to retain the data in those first 3 columns, you will either use a Dynamic Rename or Dynamic Select tool. A Dynamic Select tool with the following formula will de-select the first 3 fields in your example:

IF [Name]=="Which color was requested the most?" THEN "true"
ELSE "false" ENDIF

If you want to keep the data in those column, you could do the name replacement like you described in Excel terms. The only trouble with this is that Alteryx will force fields to have unique names. So if you replace all other field names with " ", then Alteryx will append a number to end of each duplicate field name. So two Dynamic Rename tools can be used to create "empty" field names with varying amounts of space characters.

I have attached an example.

[amberdimp1]

Thank you for your quick response, Charlie!  The data I have is in one column header.  On your example, it looks as if it shows one column for each word/phrase (unless I'm reading it wrong).  I have about 20 questions, and before each question within the same field it has the same wording.  My headers look like:

Program|Page|Face to Face Survey|Question 1

Program|Page|Face to Face Survey|Question 2

Program|Page|Face to Face Survey|Question 3

Program|Page|Face to Face Survey|Question 4 etc.

[CharlieS · Accepted solution]

Oh, in that case, all you need is a Dynamic Rename tool using the formula below.

replace([_CurrentField_],"Program|Page 1|Face to Face Survey|","")

[amberdimp1]

Thank YOU!! That is exactly what I needed. :-)

[klfmccoy]

I have a data set that has a very particular format because of how it needs to be uploaded into the system. There are ~5 column headers that are defined with text, but a number of others that are "blank" and must remain so. Alteryx automagically (ha) created header titles (F10, F11, F12...) which is great for selecting and creating formulas etc, but is there a final step I can impliment before I output the file to revert (or set) those headers to blanks? I tried using Dynamic Rename, which worked... except that it identified the blanks as "duplicate headers" and ultimately renamed them with text as a result. 

[natashulia]

Hi Charlie,

Thank you so much for your workflow!! It is very helpful!

I needed to keep the column header for only the first column and have the rest blank. Your workflow worked perfectly for all the inputs, except for the ones that had more than 10 columns.

I have attached the sample workflow with the Text Input containing 12 columns.

After I run it through your workflow, it works great for the first 10 columns, but for 11th and 12th it inserts numbers as column headers. Is there any way to have these 11th and 12th columns blank as well?

Thank you.

Best,

Natalia

[CharlieS]

@natashulia 

it looks like the problem you're having is that "Field 1" also matched to the left of "Field 11", so you get partial matches that are affecting the replacement.

I've attached a workflow that takes another approach based on position. The first field is left as is, then the other fields are renamed with a different length of space characters (so they're "unique").

A word of caution: this is a potentially dangerous way to manage data and there are good reasons why fields should have unique, visible names. However, if the case demands it, here's how it can be done. 

[natashulia]

Thank you so much, Charlie, for you help and quick response! Your workflow worked perfectly!

Thanks again!

Best,

Natalia