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!
Solved! Go to Solution.
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
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.
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?
@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.
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:
Once open, you can just change the version type to your version.
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
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".
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