ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Fill Fields Based on Previous Field

lp_hall
6 - Meteoroid

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!

AustinM
5 - Atom

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

Maskell_Rascal
12 - Quasar

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. 

 

Maskell_Rascal_0-1620162119385.png

 

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

lp_hall
6 - Meteoroid

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
6 - Meteoroid

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?

lp_hall
6 - Meteoroid

@AustinM ,

 

After a bit of tweaking, I was able to come up with a solution that can apply to multiple field names!

 

I modified the first Formula tool to read something like this:

 

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

THEN "Declined"

ELSE [Name]
ENDIF

 

On the Multi-Row Formula tool, I used the following (set to Update Existing field):

 

IF StartsWith([Name], "F")
THEN [Row-1:Name]
ELSE [Name] ENDIF

 

The difference here is rather than creating a column for the letter, i.e. A, B, I took that out and used the name contained within the column itself. So rather than using [Column letter] I used [Name].

 

The Tile tool and the second Formula tool remained the same.

 

I will give you credit for the resolution! Again thank you so much for your help! You've saved me hours of head scratching.

 

 

 

Maskell_Rascal
12 - Quasar

Hi @lp_hall 

 

Try this packed workflow. It should work for you. 

 

FYI - you can downscale any workflow by opening it as a text file in something like Notepad. It looks like this:

Maskell_Rascal_0-1620224788800.png

 

Once open, you can just change the version type to your version. 

Maskell_Rascal_1-1620224841436.png

 

Also, YXZP files are just zip files that Alteryx reads and unpacks when you open them. You can actually change the extension to .zip to see what's included in the zipped package. 

 

Thanks!

Phil

 

lp_hall
6 - Meteoroid

@Maskell_Rascal,

 

Thank you for this info! @AustinM's solution worked for me after tweaking it a bit. But this is definitely useful for future cases of changing versions and unpacking "packs".

AustinM
5 - Atom

Hey @lp_hall

 

Sorry i did not get back to your second issue that you were having. Glad my solution ended up giving you a structure to build off from!

 

Best of luck,

 

Austin

Labels