Fill Fields Based on Previous Field

Hello,

Total noob to Alteryx, this is my first workflow. I am looking for a way to fill fields to the right of the current field that is based on the current field.

In the attached sample data, you'll notice that the column headers are a combination of blanks (beginning with F) and Categories (A through H). Starting with Category A, moving to the right, I want fields 'Category A' through 'F21' to be filled with 'Category A'. Similarly, I want 'Category B' through 'F41' to be filled with 'Category B', etc.

I imagine I would have to do some kind of transpose, then some kind of fill command, then a cross tab tool to get it back into column/field form.

Additionally, after the fill has been applied, it would be great if ascending numbers could be assigned to the newly filled fields. i.e. CategoryA1, Category A2... Category B1, Category B2, etc. See the attached file titled 'FieldNames' for a complete list.

I realize that what I'm trying to accomplish can be done with a text input file, however I need the solution to be dynamic in the sense that in other examples of the input data, there may be more categories, and there may be more instances of each category. For example, some only go to 19 in this data set. However, other input data sets may go to 29, 30, 31, etc. They represent days in the month.

Thanks in advance to anyone who is able to help!

FillFieldSampleData.xlsx

FieldNames.xlsx

Workflow

Datasets

Preparation

Accepted answers

AustinM

Hello,

I gave this a quick try and came up with this solution. I'm sure there is a more brilliant method but i believe i achieved what you were looking for.

Let me know if this helped.

Thanks,

Austin

DynamicFieldName.yxmd

All comments

AustinM

Hello,

I gave this a quick try and came up with this solution. I'm sure there is a more brilliant method but i believe i achieved what you were looking for.

Let me know if this helped.

Thanks,

Austin

DynamicFieldName.yxmd

Maskell_Rascal

Hey @lp_hall

As long as your headers always contain the word "Category", then this approach is built to dynamically flip, identify, and rename them for you.

I've attached a sample workflow for you to try out.

If this solves the issue please mark answer as correct, if not let me know!

Thanks!

Phil

Fill Fields-Dynamic Rename Headers.yxzp

lp_hall

Hi @AustinM,

Thank you very much for taking time to help resolve this!

I intentionally set the different field headers to "Category A," "Category B," etc. to protect the client's confidential information, however I'm now figuring out that by using this naming sequence, it doesn't quite solve the issue I have.

Let's say each field header is a different word. Let's use some new made up headers like "Received," or "Declined," for example.

On the first Formula tool in your workflow, would it make sense to modify the formula to something like this?

IF StartsWith([Name], "Received")
THEN "Received " + Right([Name], 1)
ELSEIF StartsWith([Name], "Declined")

THEN "Declined " + Right([Name], 1)

ELSE [Name]

ENDIF

Similarly, on the second Formula tool, would I use something like this?

IF Left([Name], 1) = "F"
THEN "Received " + [Column letter]
ELSEIF Left([Name], 1) = "F"
THEN "Declined " + [Column letter]

ELSE [Name]
ENDIF

I've personally tried modifying the Formula tools myself using the methodology above, but it doesn't quite result in the output I was looking for. You've definitely gotten me a lot further than I was before though! Thank you for that.

lp_hall

Hi @Maskell_Rascal,

Thank you very much for taking time to help resolve my issue!

I was unable to open your workflow because it is a package, and it is built in what appears to be the latest version of Designer. The version of Alteryx I have available to me is 2019.3.1.24384. I'm not sure if you're able to save your workflow to that version. Your help is appreciated though! Perhaps you can try resaving it?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703